Deleting rows that meet specific criteria

[raeannb]

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:

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:

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:

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!!!

 

[Jonmo1]

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]

It's always better to work from the bottom up when deleting rows. Try this on a copy of your data.

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]

SO SMART! I had no idea you could move backwards! Thank you tons. This just made my day.

 

[Jonmo1]

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..