Code in VBA loops and never ends. How to fix this?
I run this code to delete rows which have have > -100. However it keeps looping and never stops.
What am I missing here?
For i = 2 To 500
If Worksheets("Sales").Cells(i, 3).Value > -100 Then
Worksheets("Sales").Cells(i, 3).EntireRow.Delete
i = i - 1
End If
Next i
Maybe you could union the rows and delete them at once? Something like this (untested).
Dim myRow As Range
Dim toDelete As Range
For i = 2 To 500
If Worksheets("Sales").Cells(i, 3).Value > -100 Then
Set myRow = Worksheets("Sales").Rows(i)
If toDelete Is Nothing Then
Set toDelete = myRow
Else
Set toDelete = Union(toDelete, myRow)
End If
End If
Next i
If Not toDelete Is Nothing Then _
toDelete.Delete
When deleting or inserting rows in VBA you need to start with the last row and move toward the first, since any given row will get lost in the loop once it's deleted or new rows are inserted.
See the code below:
For i = 500 To 2 Step -1
If Worksheets("Sales").Cells(i, 3).Value > -100 Then
Worksheets("Sales").Cells(i, 3).EntireRow.Delete
End If
Next I
EDIT
I just thought of this as well, which will run much faster (especially if you had a lot more than 500 cells):
With Worksheets("Sales")
'assumes row 1 is headers
.Range("C1:C500").AutoFilter 1, ">-100"
.Range("C2:C500").SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With