Delete rows with different criteria

[Guest]

I have a simple macro that filters (hides) the data that is unapplicable in
each worksheet. How would I make the macro delete the rows that it is hiding
in the autofilter? My code is below.

THANKS!

Selection.AutoFilter Field:=8, Criteria1:="<>", Operator:=xlAnd
Selection.AutoFilter Field:=14, Criteria1:=">0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd
Sheets("AG Non Call").Select
Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd
Selection.AutoFilter Field:=14, Criteria1:=">0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd
Sheets("CORPS").Select
Selection.AutoFilter Field:=14, Criteria1:=">0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="=8.00"

 

[Guest]

change your criteria to show ONLY THE LINES YOU PREVIOUSLY HAD HIDDEN and
then delete all rows


Selection.AutoFilter Field:=8, Criteria1:="<>", Operator:=xlAnd
Selection.AutoFilter Field:=14, Criteria1:="=0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="<>7.00", Operator:=xlAnd
activesheet.usedrange.delete
Sheets("AG Non Call").Select
Selection.AutoFilter Field:=8, Criteria1:="<>#N/A N Ap", Operator:=xlAnd
Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="<>7.00", Operator:=xlAnd
Sheets("CORPS").Select
Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="<>8.00"
activesheet.usedrange.delete

test this code before finalizing and saving

 

[Guest]

ben, I will try that, but you didn't change the first line... should that
change?

Selection.AutoFilter Field:=8, Criteria1:="<>", Operator:=xlAnd

 

[STEVE BELL]

You can use something like this to select visible cells
and than copy them to another location

Selection.SpecialCells(xlCellTypeVisible).Select

or something like this

Dim rw As Long, lrw As Long
lrw = Cells(Rows.Count, "A").End(xlUp).Row

For rw = lrw To 1 Step -1
If Rows(re).Hidden = True Then
MsgBox Rows(x).Address
End If
Next

 

[Guest]

That didn't quite work. I don't think I can delete the "Used range" because
I have stuff in rows 1 through 10 that I want to keep. Does that make sense?

I think it may also be hiding all the fields and not filtering them
correctly. I use 700 rows and on two of the sheets 700 rows are hidden...
none are deleted.

 

[Guest]

play with your criteria a little until you have only the rows showing you
want to delete
then
activesheet.rows("11:2000").delete

 

[Jef Gorbach]

Since there is not a SpecialCells(xlCellTypeHidden), reverse your criteria
to hide the data to be kept within your range then delete the remaining
visible rows.

(untested)

Selection.AutoFilter Field:=8, Criteria1:="<>", Operator:=xlAnd
Selection.AutoFilter Field:=14, Criteria1:=">0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd
Sheets("AG Non Call").Select
Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd
Selection.AutoFilter Field:=14, Criteria1:=">0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd
Sheets("CORPS").Select
Selection.AutoFilter Field:=14, Criteria1:=">0", Operator:=xlAnd
Selection.AutoFilter Field:=18, Criteria1:="=8.00"

Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[Guest]

Well, as I am working on getting the right criteria...

Ben your second delete function works, the other ones don't... they give me
an error(400).

thanks for the help!