excel - For Loop not iterating VBA

Question

I am trying to delete an entire row if the values in row i column 2 and row i column 3 are empty. My for loop only iterates once. Any idea why?

Sub DeleteEm()

RowCount = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To RowCount
    If IsEmpty(Cells(i, 2).Value) = True And IsEmpty(Cells(i, 3).Value) = True Then
        Rows(i).EntireRow.Delete
    End If
Next i

End Sub

Thank you!

question from:https://stackoverflow.com/questions/65852490/for-loop-not-iterating-vba

Answer 1

You have made an error which is very common for newbies in VBA. To correct the error you need to change

For i = 2 To RowCount

to

For i = RowCount to 2 Step -1

Your original code is deleting rows within the range over which you are iterating.

Consider what happens when i=4 and you delete the row corresponding to that i. Row 4 is deleted. What was row 5 now becomes row 4 BUT at the Next, i becomes 5 so i is now pointing at what was row 6 in your starting range having skipped over what was previously row 5, because that became row 4.

If you use F8 to step through your code whilst watching your sheet you will see it all happen before your eyes.