is there a way delete rows based on cell values

[vvkelley]

Hi Folks,
I had a problem with a dataset I was working with and was wondering if anyone knew what I should do. I'm looking at the value's in a particular column and want to delete the entire row for anything with a specific combination of letters. Does anyone know how to do this systematically? I could hide them by filtering I know, but is there a way to delete them out right? I'm using excel 2007 on a windows 7 os. Many thanks for any help.

 

[jeffreybrown]

Here are a couple of options which employ the autofilter method.

I don't know how many requirements you have to delete so the first handles one and the second multiple.

Just abjust the sheet name and the column range.

Sub DelRows2()
    Application.ScreenUpdating = False
    With Sheets("Cases").Range("C1", Range("C" & Rows.Count).End(xlUp))
        .AutoFilter field:=1, Criteria1:="Yes"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Sub DelRows1()
    Application.ScreenUpdating = False
    With Sheets("Sheet1").Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter field:=1, Criteria1:=Array("AAAA", "BBBB", "EEEE"), Operator:=xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[AlphaFrog]

Could you do a filter where only the rows you want to delete are visible and all the others are hidden? Then you can select the visible rows and delete them. Then turn off the filter.

 

[vvkelley]

Yeah, that would do it. Thanks.