why is my loop so slow?
 

what i need to do is delete any rows where col b = 0 or 2?


For conCat2 = 2 To iCountA
If ActiveCell.Value = 2 Then
ActiveCell.EntireRow.Delete
ElseIf ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete

Else
ActiveCell.Offset(1, 0).Activate
End If
Next

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

why is my loop so slow?
 

Try


Sub AAA()
Dim RangeToDelete As Range
Dim iCountA
Dim RowNdx As Long
iCountA = 100
For RowNdx = 2 To iCountA
If Rows(RowNdx).Cells(1, "B") = 0 Or _
Rows(RowNdx).Cells(1, "B") = 2 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = Rows(RowNdx)
Else
Set RangeToDelete = Application.Union(RangeToDelete,
Rows(RowNdx))
End If
End If
Next RowNdx
RangeToDelete.EntireRow.Delete
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Buffyslay" wrote in message
oups.com...
what i need to do is delete any rows where col b = 0 or 2?


For conCat2 = 2 To iCountA
If ActiveCell.Value = 2 Then
ActiveCell.EntireRow.Delete
ElseIf ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete

Else
ActiveCell.Offset(1, 0).Activate
End If
Next

why is my loop so slow?
 

Wow - a veritable masterclass in less than twenty lines of code!

Top man Chip. ?:^)

Buffyslay, be sure to check out his website.

NickH