Is is possible to write a macro that would AutoFilter a column by using the
exact or wildcard data entered in a cell; say "A1" instead of using an input
box? If so, what would the code look like?

Assume you have data in Columns A:E and want to filter on column C for a
value that "contains" a string.

If you record a macro filtering on "contains" you will get similar to this.

Sub Macro2()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=*ay*"
End Sub

Change Selection.AutoFilter Field:=3, Criteria1:="=*ay*" to

Selection.AutoFilter Field:=3, Criteria1:="=*" & Range("H1").Value & "*"

Note that H1 is outside the range of data. A1 would be reserved for AF
header row so A1 might not be a good cell to reference.


Gord Dibben MS Excel MVP


On Mon, 16 Feb 2009 12:22:00 -0800, Tony S.
wrote:

Is is possible to write a macro that would AutoFilter a column by using the
exact or wildcard data entered in a cell; say "A1" instead of using an input
box? If so, what would the code look like?

Excellent Gord, Thanks for the guidance.

"Gord Dibben" wrote:

Assume you have data in Columns A:E and want to filter on column C for a
value that "contains" a string.

If you record a macro filtering on "contains" you will get similar to this.

Sub Macro2()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=*ay*"
End Sub

Change Selection.AutoFilter Field:=3, Criteria1:="=*ay*" to

Selection.AutoFilter Field:=3, Criteria1:="=*" & Range("H1").Value & "*"

Note that H1 is outside the range of data. A1 would be reserved for AF
header row so A1 might not be a good cell to reference.


Gord Dibben MS Excel MVP


On Mon, 16 Feb 2009 12:22:00 -0800, Tony S.
wrote:

Is is possible to write a macro that would AutoFilter a column by using the
exact or wildcard data entered in a cell; say "A1" instead of using an input
box? If so, what would the code look like?