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
