Take the 2-minute tour ×

I'm looking to automatically delete rows in Excel 2007 based on whether or not there is a cell with a specific value in that row. I've Googled around, but I've only found old information on VBA scripts for previous versions of Excel and so far all of it is non-functional. I'm looking for a SuperUser quality explanation on how to do this.

share|improve this question

1 Answer 1

This macro will delete rows that contain cells with the "fri" value, and then shift the cells up. Just change "FRI" in the code to the value you'd like to search for. You could also modify it to prompt for a value or use the value from another cell as a criteria.

Sub DeleteThis()

For Each Rng In Selection.Rows
    If (Application.WorksheetFunction.CountIf(Rng, "fri") > 0) Then
        Rng.Delete Shift:=xlUp
    End If
Next Rng

End Sub

To use: Select your data range, then run the macro.

enter image description here

enter image description here

Non-VBA: You can use Excel's Advanced Filters to select rows that meet certain criteria; although, it might require some sheet real estate and a few more steps.

share|improve this answer
This seems ideal at first, but I'm getting a 'next without for' compile error from VB. Also, would it be possible to customize the cell requirements with a regular expression? –  hisnameisjimmy Aug 17 '11 at 18:18
I'm running Excel2007. It's not giving me the next without for error when I run it. As for regex, I'm not particularly sure how to use it with VBA. With the macro above, you can, however, use conditions that the formula COUNTIF supports. –  Ellesa Aug 17 '11 at 20:40
For what it's worth, you can use wildcards in the COUNTIF as such: Application.WorksheetFunction.CountIf(Rng, "^" & "fri" & "^") Edit: okay, asterisks don't display in comments. Just imagine those carets are asterisks. –  variant Aug 18 '11 at 17:19
Not sure why, but this macro only seemed to remove the first match and then ignore the rest :S –  JDandChips Oct 1 '12 at 13:52

Your Answer


