VBA Loop Is Missing Rows While Deleting Rows

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.

Excel showing records to be deleted in loop

Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Insert a module in the VB editor to write loop to delete records

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.

Insert a module in the VB editor

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




vba code to delete records without missing records

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.

result showing rows were not skipped while deleting records using vba loop

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.




Share The Knowledge

Random Posts