Slow (looping) macro - Need help adjusting it

**PY_** · 09-07-2011, 04:21 PM

I know this macro is not the most efficient macro just because of the loops and how long it takes my machine to run. Is there another way to write this so it doesnt make my 5yr+ old machine just come to a halt while running it?

' Lock Open Walls Cells - Partition Walls - Framed Openings - Below Eave Canopies
    Private Sub lockCells_OpenWalls()
            Dim i As Long
            Dim j As Long
            Dim k As Long
    ' Lock Framed Opening Cells
        Sheets("Gen_3").Activate
        ActiveSheet.Unprotect Password:="MyPassword"
        For i = 278 To 341
            If Range("G" & i).Value = "" Or _
                Range("G" & i).Value = "n/a" Then
                    Range("O" & i & ":V" & i).Locked = True
                    Range("X" & i & ":AF" & i).Locked = True
                    End If
            If InStr(Range("G" & i).Value, "Open") Then
                Range("O" & i & ":V" & i).Locked = False
                Range("X" & i & ":AF" & i).Locked = True
                End If
            If InStr(Range("C" & i).Value, "Front") Or _
                InStr(Range("C" & i).Value, "Back") Then
                    Range("X" & i & ":AF" & i).Locked = True
                    End If
            If Range("x" & i).Value = "Select Endwall Type" Then
                Range("X" & i & ":AF" & i).Locked = False
                End If
            Next i
    ' Lock Framed Opening Cells
        For j = 494 To 597
            If Range("G" & j).Value = "" Then
                Range("N" & j & ":AH" & j).Locked = True
                End If
            Next j
    ' Lock Below Eave Canopies
        For k = 346 To 473
            If Range("G" & k).Value = "" Or _
                Range("G" & k).Value = "n/a" Or _
                InStr(Range("C" & k).Value, "Soffit") Then
                    Range("Q" & k & ":AH" & k).Locked = True
                    End If
                Range("U" & k).Locked = True
            Next k
End Sub

**Fettertiger** · 09-08-2011, 07:26 PM

Hi,

maybe you should add following commands after the Variable Declarations:

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Before the End Sub, you will need to set them back to normal:

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

Regards

Theo
__________________
Please consider:

Thanking those who helped you. Click the scales icon in the upper right corner of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

**mudraker** · 09-08-2011, 07:51 PM

Hi

You refer to the same cell multiple times - it is quicker to put the cell value into memory as a variable

The way you i loop is coded you have the potential to lock and unlock the same cells multiple times

this is 1 way to overcome that problem - I have only looked at fixing your i loop

Note:- with the instr command using "Front|Back" I have never used the | to join to items but got the info from

Dim i As Long
Dim j As Long
Dim k As Long

Dim bOV As Boolean
Dim bXAF As Boolean
Dim sTxt As String

' Lock Framed Opening Cells
Sheets("Gen_3").Activate
ActiveSheet.Unprotect Password:="MyPassword"

For i = 278 To 341
sTxt = Range("G" & i).Value
Select Case sTxt
Case "", "n/a"
bOV = True
bXAF = True
End Select
If InStr(sTxt, "Open") Then
bOV = False
bXAF = True
End If

sTxt = Range("C" & i).Value
'*****************************************************************
'you could change this use Or instead of an ElseIf
'I do not know the speed difference between the 2 methods
'*******************************************************************
If InStr(sTxt, "Front") Then
bXAF = True
ElseIf nStr(sTxt, "Back") Then
bXAF = True
End If

If Range("x" & i).Value = "Select Endwall Type" Then
bXAF = False
End If

Range("O" & i & ":V" & i).Locked = bOV
Range("X" & i & ":AF" & i).Locked = bXAF
Next i