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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
 
Upvote 0
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..
 
Upvote 0

Forum statistics

Threads
1,223,461
Messages
6,172,357
Members
452,455
Latest member
Kobus Horn

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
Back
Top