excel-vba Common Mistakes Deleting rows or columns in a loop


Example

If you want to delete rows (or columns) in a loop, you should always loop starting from the end of range and move back in every step. In case of using the code:

Dim i As Long
With Workbooks("Book1").Worksheets("Sheet1")
    For i = 1 To 4
        If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete
    Next i
End With

You will miss some rows. For example, if the code deletes row 3, then row 4 becomes row 3. However, variable i will change to 4. So, in this case the code will miss one row and check another, which wasn't in range previously.

The right code would be

Dim i As Long
With Workbooks("Book1").Worksheets("Sheet1")
    For i = 4 To 1 Step -1
        If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete
    Next i
End With