Deleting Rows With VBA By Looping (Correctly)
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 SubUnfortunately, 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!
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 SubNotice 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 SubI 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.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
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.