# Deleting rows that meet specific criteria

#### raeannb

##### Board Regular
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

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

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

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

Replies
15
Views
236
Replies
6
Views
131
Replies
2
Views
206
Replies
11
Views
446
Replies
2
Views
307

1,203,105
Messages
6,053,544
Members
444,670
Latest member
laurenmjones1111

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

### Which adblocker are you using?

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

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