Generalising Deleting Rows after AUTOFILTER

[jason]

I have this code:

Sub Macro2()

Range("A1").AutoFilter Field:=1, Criteria1:="b"
Rows("7:9").Delete Shift:=xlUp
Selection.AutoFilter

End Sub

and its used on a small table like this:

Name Age
a 5
c 75
c 96
c 4
c 8
b 59
b 8
b 7

I've got no way of predicting how many rows there'll be in the table,
so how do I generalise the second row of the code? i.e the bit
'Rows("7:9")'.
Or do I need to look into an alternative approach?

Any help greatly appreciated,
Jason.

 

[Tom Ogilvy]

Sub Macro2()
Dim rng As Range
Range("A1").AutoFilter Field:=1, Criteria1:="b"
Set rng = ActiveSheet.AutoFilter.Range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
rng.EntireRow.Delete
Range("A1").AutoFilter
End Sub


Will delete all the entries with b

Test on a copy of your data.

 

[Guest]

hi.
there is no way to generalize code. code is specific in
what it does.
you will have to add code that will find and select what
you wish to delete.
this line will open the find dialog box
Application.Dialogs(xlDialogFormulaFind).Show

 

[jason]

Cheers Tom
I think this little beauty'll be getting used a lot
Jason