3

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.

Improve this question

1 Answer 1

Reset to default
2

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.

sample:
before:
enter image description here

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

Improve this answer
4
  • 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
    Commented Aug 17, 2011 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
    Commented Aug 17, 2011 at 20:40
  • 1
    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
    Commented Aug 18, 2011 at 17:19
  • Not sure why, but this macro only seemed to remove the first match and then ignore the rest :S
    – JDandChips
    Commented Oct 1, 2012 at 13:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .