×

Deleting Rows With VBA By Looping (Correctly)

By Chris Newman •  Updated: 10/08/22 •  5 min read
How to delete rows with VBA by looping.

Deleting Rows With For Each Loop Misses Rows

When I first attempted to delete rows using VBA code I thought it was going to be a piece of cake. I went into the VB Editor and typed out a simple For Each Loop in order to delete each row that contained the word ‘Product C’. With only a few lines of code, I was ready to test it out.

Sub Delete_Product_C()

Dim Cell As Range

'Loop through range and delete [Product C] rows
    For Each Cell In ActiveSheet.UsedRange.Columns(1).Cells
        If Cell.Value = "Product C" Then Cell.EntireRow.Delete
    Next Cell

MsgBox "Rows Deleted!"

End Sub

Unfortunately, after running the simple For Each loop code, there was still a row with “Product C” left!

Why was this? I clearly wrote VBA to delete all the rows with “Product C” in them!

Deleting rows with VBA with For Each Loop.

Why You Can’t Use For Each Loops To Delete Rows

Well, when you take the time to think about what my For Each Loop was doing it was deleting a row and then moving on to the next row. 

The problem with this is that when you delete a row, it affects (shifts) the row numbers of everything below the row that was just deleted.  For instance, when Row 3 was deleted in our above example, Row 4 turned into Row 3 but the For Each Loop didn’t care!  The loop just went on to the next row (Row 4).

How To Correctly Delete Rows With VBA Loops

So if you can’t use a For Each loop, then how do you properly account for the row numbers shifting after your VBA code deletes a row? There are two solutions you can use that both rely on a For loop.

Loop In Reverse Method

The Loop in Reverse method starts at the bottom of your data set and works its way up. By doing this, the shifting of row numbers doesn’t impact your code.

There is a little add-on to the For Loop you can use to modify the loop’s direction and interval.  This add-on is called Step

We can use Step by starting with the last row in our data set and stepping backward in our loop by an interval of 1.  Take a look at the VBA code below to see this little feature in action.  

Sub Really_Delete_Product_C()

Dim x As Long

'Loop through range and delete [Product C] rows
    With ActiveSheet.UsedRange
        For x = .Rows.Count To 1 Step -1
            If .Cells(x, 1).Value = "Product C" Then
                .Rows(x).EntireRow.Delete
            End If
        Next x
    End With

End Sub

Notice by adding “Step -1” you are telling variable x that at the end of the loop you want x to subtract 1 from itself instead of doing the default of adding 1 to itself. 

Loop With A Step Back

An alternate way that you can delete rows without skipping any lines is by stepping your looping variable (x in my example) back by 1 if a row is in fact deleted. 

Inside your If/Then statement, which only allows rows that are getting deleted to enter, you can subtract 1 from your looping variable x so that when you get to the Next x statement or x value is repeated. 

So your loop would follow this pattern:
      enter loop: x = 3 —> delete row 3 —> x = 2 —> next x —> exit loop: x = 3

Sub Really_Delete_Product_C()

Dim x As Long

'Loop through range and delete [Product C] rows
    With ActiveSheet.UsedRange
        For x = 1 To .Rows.Count
            If .Cells(x, 1).Value = "Product C" Then
                .Rows(x).EntireRow.Delete
                x = x - 1
            End If
        Next x
    End With

End Sub

I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can properly use loops in VBA code to delete cell rows without accidentally skipping deletions. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

[FREE Training] 10 Amazing Excel Efficiency Tricks. Why Don't People Know These?!

X