How to automatically delete rows in Excel 2007 based on cell value?

Question

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.

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.

sample:
before:

after:

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.