In this post we will see how to fix the loop in VBA if the loop is skipping the rows after deleting the rows, there are several programming situation in which we are required to delete rows in excel based on some condition and we have observed that VBA loop needs to run multiple times before the loop is able to delete all the rows where the condition is met.
This generally happens because the loop misses its index when a row suddenly disappears when deleting a row and we will see how to prevent VBA loop from skipping the row after a delete operation in the loop.
Below is the data range and we would like to delete row if the cell value contains 2, 3 or 5 i.e. VBA loop will check each of the cell and if the value is in 2, 3 or 5, then it will be deleted otherwise the loop will move to next iteration.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA loop to delete rows based on some condition without missing or skipping rows.
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Sub Sample()
With Worksheets(“Sheet1”)
For rw = .Cells(.Rows.Count, “A”).End(xlUp).Row To 2 Step -1
Select Case UCase(.Cells(rw, “A”).Value2)
Case 2, 3, 5
.Rows(rw).EntireRow.Delete
End Select
Next rw
End With
End Sub
The solution is to write the loop from bottom up so that If we delete a row, everything moves up and the row is not skipped in the index.
As we see in the code, the loop is running from bottom to up and checking the cell value for the condition and the row is deleted if the condition is true.
We can see the result that we have got the desired result and VBA loop has not missed any rows while deleting rows in loop.
Hope this helped.
