AutoFilter based on cell value
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? |
AutoFilter based on cell value
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? |
AutoFilter based on cell value
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? |
All times are GMT +1. The time now is 12:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com