View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
NickH NickH is offline
external usenet poster
 
Posts: 60
Default why is my loop so slow?

Hi Buffyslay,

No need to activate cells and make sure ScreenUpdating is off. Also, if
your workbook makes heavy use of formulas this can have a significant
impact on speed.

Try something like this (untested)...

Sub BuffysLoopy()
Dim i As Long
Dim CalcState As Integer

''' Record the current calculation setting
CalcState = Application.Calculation
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With

For i = iCountA To 2 Step -1
If Cells(i, 2) = 0 Or Cells(i, 2) = 2 Then
Cells(i, 2).EntireRow.Delete
End If
Next i

''' Restore original calculation setting
With Application
.ScreenUpdating = True
.Calculation = CalcState
End With
End Sub