Deleting rows that meet specific criteria

raeannb

Board Regular
Joined
Jun 21, 2011
Messages
86
I have a simplish problem that I hope has an equally simple answer - I've been using clumsy work-arounds to deal with it, but I KNOW there has to be a better way. Here it is:

I often use a loop to delete rows that meet certain criteria. For example:

Code:
For i = 1 to x
If Cells(i, 3).Value = "Blah" Then
Rows(i).EntireRow.Delete
End if
Next i

The problem with this code arises if I have two consecutive "Blah" rows. If I delete row i, then the row below it suddenly becomes the new i-th row. However, since row i was already evaluated by the If statement, this new i-th row does not get evaluated, and the loop moves on to the Next i. Does this make sense? I feel like I might be explaining this weirdly.

One fix that I've tried only works when you know the maximum number of repeats you could have. Basically, if you'll never have more than 2 consecutive "Blah" rows , you can do the following:

Code:
For j = 1 to 2
For i = 1 to x
If Cells(i, 3).Value = "Blah" Then
Rows(i).EntireRow.Delete
End if
Next i
Next j

This just does the loop twice, presumably cleaning up any remaining "Blah" rows that were missed the first time around. Ideally, I'd like something more flexible, that I can use when I have many repeated "Blahs" without having to spend 20 minutes looping.

Another solution I've used is to first sort so that the "Blahs" are at the bottom, then delete everything after the first "Blah" is found:

Code:
For i = 1 To x
    If Cells(i, 1).Value = "Blah" Then
        Do Until Cells(i, 1).Value = ""
        Rows(i).EntireRow.Delete
            If .Cells(i, 1).Value = "" Then Exit Do
        Loop
    End If
Next i

The problem with this one is that I'm using an empty cell to tell the loop to stop, but sometimes I want to use emptiness as my deletion criteria (in other words, empty cell = "Blah"), so that makes this annoying.

I feel like there's probably an awesome, mind-blowingly simple way to solve this, but I am stumped. Any suggestions?

Thank you SO MUCH for your help!!!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
This is a common problem.
And you're already a step ahead because you understand why it's a problem.....

The solution is to go backwards (from bottom to top)

Try

For i = x to 1 Step -1
 

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
It's always better to work from the bottom up when deleting rows. Try this on a copy of your data.
Code:
Sub DeleteBlah()
Application.ScreenUpdating = False

      Dim LR As Long, i As Long
LR = Range("A" & Rows.count).End(xlUp).Row 'change to suit the column to evaluate
For i = LR To 1 Step -1
    If Range("A" & i).Value = "Blah" Then Rows(i).Delete
Next i

Application.ScreenUpdating = True


End Sub
 

raeannb

Board Regular
Joined
Jun 21, 2011
Messages
86
SO SMART! I had no idea you could move backwards! Thank you tons. This just made my day.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
What's even cooler...

You can use Step to only execute on every 2nd (or 3rd or 4th etc) row

For i = 1 to x Step 3

goes 1 4 7 10 etc..
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top