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.

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.

--
Regards,
Tom Ogilvy


"jason" wrote in message
om...
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.

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


"Tom Ogilvy" wrote in message ...
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.

--
Regards,
Tom Ogilvy


"jason" wrote in message
om...
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.

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

-----Original Message-----
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.
.