Filter and Delete Rows

[gbloemke]

I use Excel 2010. I run VBA code on this one table everyday. I currently have to do it partly manually, because it deletes more rows than I want it to.

My goal is to filter the spreadsheet using this criteria:
Filter column 22 = "NEP", also filter column 16 = "Groom".
Then, I wish to delete all rows left after filter that contain "ONSP" or "Offnet" in column G1.
This last statement doesn't work. Instead, it deletes ALL rows that contain "ONSP" or "Offnet", whether filtered or not.

Here's my code (I hope it makes sense):

-------------------------------------------------------
Sub FilterAndDelete()
' Filter for all ONSP tasks from column G

Range("V1").Select
Selection.AutoFilter
Range("$A$1:$W$7000").AutoFilter Field:=22, Criteria1:="NEP"
Range("$A$1:$W$7000").AutoFilter Field:=16, Criteria1:="Groom"
Range("G1").Select
ActiveSheet.Range("$A$1:$W$7000").AutoFilter Field:=7, Criteria1:="=ONSP*" _
, Operator:=xlOr, Criteria2:="=Offnet*"

End Sub
--------------------------------------------------------
Thanks,
Greg

 

[PHV]

Did you try this ?
Range("$A$1:$W$7000").AutoFilter Field:=22, Criteria1:="NEP"
Range("$A$1:$W$7000").AutoFilter Field:=16, Criteria1:="Groom"
Range("$A$1:$W$7000").AutoFilter Field:=7, Criteria1:="=ONSP*", Operator:=xlOr, Criteria2:="=Offnet*"


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

see it this is what you need...

Sub FilterAndDelete()
' Filter for all ONSP tasks from column G

    With ActiveSheet.UsedRange
        .AutoFilter
    
        .AutoFilter Field:=22, Criteria1:="NEP"
        .AutoFilter Field:=16, Criteria1:="Groom"
        .AutoFilter Field:=7, _
            Criteria1:="=ONSP*", Operator:=xlOr, _
            Criteria2:="=Offnet*"
    
    '[highlight][b]assuming that these VISIBLE ROWS are the rows that you want deleted...[/b]
        Range(.Cells(2, "A"), .Cells(.Rows.Count, "A")).SpecialCells(xlCellTypeVisible).Delete  '[/highlight]
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gbloemke]

I am sorry, I mispoke. This code works fine for filtering the data. I want to delete all rows that meet the filter criteria. It's the code that comes next that I am struggling.

 

[SkipVought]

...and???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gbloemke]

Okay Skip, that was awesome? It worked! You just showed me a new way to do this, and I will remember the whole "With ActiveSheet.UsedRange" simplier thought process for my next code! Thank you.

Question, after this code runs, I get a pop-up dialog box that says "Delete entire sheet row?" and it has a checkbox of "OK" or "Cancel". I choose OK and that finishes the delete command. Since I will always choose OK, can the code be changed or added to automatically answer that dialog box? I'd prefer to not get that dialog box.

Thanks again.
Greg

 

[PHV]

Have a look at the Application.DisplayAlerts property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Sub FilterAndDelete()
' Filter for all ONSP tasks from column G

    With ActiveSheet.UsedRange
        .AutoFilter
    
        .AutoFilter Field:=22, Criteria1:="NEP"
        .AutoFilter Field:=16, Criteria1:="Groom"
        .AutoFilter Field:=7, _
            Criteria1:="=ONSP*", Operator:=xlOr, _
            Criteria2:="=Offnet*"
    
    'assuming that these VISIBLE ROWS are the rows that you want deleted...
        Application.DisplayAlerts = False
        Range(.Cells(2, "A"), .Cells(.Rows.Count, "A")).SpecialCells(xlCellTypeVisible).Delete  
        Application.DisplayAlerts = True
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gbloemke]

Skip, you're my new hero. It all works, and I learned a lot as well. Thanks and have a great weekend.

Greg

 

[gbloemke]

Thanks to PHV as well!