AutoFilter based on cell value

  • Thread starter Thread starter Tony S.
  • Start date Start date
T

Tony S.

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
 
Excellent Gord, Thanks for the guidance.

Gord Dibben said:
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
Click to expand...
 
Back
Top