How to automatically delete certain rows in Excel?

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

• 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:

  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.