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

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



"Paul Ponzelli" wrote in
message ...
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

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

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

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

HTH. Best wishes Harald

"Paul Ponzelli" skrev i melding
...
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

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

Paul Ponzelli wrote:

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

--

Dave Peterson