Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

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

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
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 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.