Automatic Filtering

[sjamadok]

Hi there,

Is there a way to create a filter that accomplishes the following:

Example Speradsheet
A B
1
2 City State
3 Phoenix AZ
4 Los Angeles CA
5 San Francisco CA

I would like to filter the above data by the state of CA just by typing "CA"
into cell B1 and hitting enter.

I tried playing with advanced filtering, but every time I wish to filter on
a different state, I must go to Data, Filter, Advanced Filter. I could make
a macro to do this part, but was wondering if there is a different kind of
filter I could use.

 

[Fred Smith]

Yes, a macro could do the job, but it sounds overly complicated to me. Why not
just use AutoFilter? (not Advanced, just the normal one). It will give you a
drop down arrow in your State cell, you click on it and select CA. It's at least
as easy, if not easier, than typing CA in another cell.

If you're having trouble turning it on, it may be because row 1 is blank. Either
delete row 1, or highlight row 2 when you turn it on.

 

[sjamadok]

Thanks for your thoughts Fred.
I use auto filters a lot, but I'm trying to figure out a quicker way. For
example, if I want to filter on, say WV, I have to scroll all the way to the
bottom of the auto filter. I could also choose "Custom..." in the auto
filter and use 'is equal to'. However, it would be much more efficient if I
could just type the state into a cell and have the spreadsheet filter based

 

[Fred Smith]

The macro you want is a simple one-liner:

Sub SelectState()
Selection.AutoFilter Field:=2, Criteria1:=Range("B1")
End Sub

When you create the macro, you can assign it to a control key, like a. You would
then enter WV in B1, and hit Ctrl-a.

Let me know how it works.

 

[sjamadok]

That works wonderfully. Thanks Fred!

The macro you want is a simple one-liner:

Sub SelectState()
Selection.AutoFilter Field:=2, Criteria1:=Range("B1")
End Sub

When you create the macro, you can assign it to a control key, like a. You would
then enter WV in B1, and hit Ctrl-a.

Let me know how it works.