selecting an AutoFilter value in code

[Paul Ponzelli]

Is there a way to set the value of an AutoFilter drop box in code?

Assume you've already run the following code to show all records:

If Sheets("Address").FilterMode Then
Sheets("Address").ShowAllData
End If

Is there a line of code you can run to filter the records by a particular
value in one of the autofilter drop boxes? Let's say, for example, that one
of the values in the AutoFilter drop box in cell C1 is "Texas." Is there a
way to set that value in the AutoFilter to "Texas"?

Thanks in advance,

Paul

 

[Chip Pearson]

Try something like


Dim WS As Worksheet
Set WS = ActiveSheet
WS.UsedRange.AutoFilter field:=1, Criteria1:="Texas"

Change the 1 to the column number of the column you are filtering
on.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Harald Staff]

Sure Paul. Record a macro while doing it manually for syntax.

HTH. Best wishes Harald

 

[Dave Peterson]

If you record a macro when you do it manually, you'll see the code.

 

[Paul Ponzelli]

Works great, Chip, thanks.

My thanks also to Harald and Dave for their suggestions about recording the
macro to get the code example. I forgot about that feature in Excel.

Paul

 

[Paul Ponzelli]

This is a belated thank you to Chip, Harald and Dave for offering two
different solutions to my question. I originally sent this thank you
shortly after you posted your messages, but for some reason it didn't seem
to make it to the newsgroup.

In any event, thank you for your help, gentlemen.

Paul