Auto filter to work from cell reference

[Corleone]

is is possible to do the above
e.g to have the auto filter set up so that it selects the option based on
the value in cell "d9" ?

or is easier to do via a macro?

thanks

 

[Corleone]

Ive stumpled upon a part soloution to this by using the following code

Sub FILTER_AREA()
Dim bf As String
bf = Range("AREA").Value
Selection.AutoFilter Field:=3, Criteria1:="=*" & bf & "*", Operator:=xlAnd
End Sub

this appears to work by populating the custom dropdown in the autofilter with the cell content in the "AREA" range (cell "d9" on another worksheet)

however the problem i have is if the cell that it refers to in the range name (which is a dropdown) is selected as "(all)" when the macro runs, the filter returns no rows instead of all of them. as "(all)" is not an option in the custom filter dropdown

is their a way of adding into the above code so that if the cell is on (all) the autofilter selects
"(all)" from the filter dropdown options?
thanks

 

[Corleone]

cheeky bump for the daytimers

 

[VoG]

Try (untested)

Sub FILTER_AREA()
Dim bf As String
bf = Range("AREA").Value
Select Case bf
    Case "(all)"
        Selection.AutoFilter Field:=3
    Case Else
        Selection.AutoFilter Field:=3, Criteria1:="=*" & bf & "*", Operator:=xlAnd
End Select
End Sub

 

[Corleone]

Try (untested)

Sub FILTER_AREA()
Dim bf As String
bf = Range("AREA").Value
Select Case bf
    Case "(all)"
        Selection.AutoFilter Field:=3
    Case Else
        Selection.AutoFilter Field:=3, Criteria1:="=*" & bf & "*", Operator:=xlAnd
End Select
End Sub

thanks for the reply

this doesnt quite work because the first section of the code looks for "(all)" as a category under the custom dropdown (which doesnt exist) , what i need it to do instead is for it to
select the first (all) which appears in the filter dropdown

 

[Corleone]

thanks for the reply

this doesnt quite work because the first section of the code looks for "(all)" as a category under the custom dropdown (which doesnt exist) , what i need it to do instead is for it to
select the first (all) which appears in the filter dropdown

My mistake, your code above works perfectly

thats another beer I owe you