Filter Records based on the value selected from a drop-down list b

[Brenner]

Hi All -

First let me say that you all have been a great help in the past! )

This is a pretty simple question. I've created a drop-down list box in cell
F1 (fed from another sheet), and the values in Range F5:F200 match one of the
values in the list. So - when I select the value in the list-box, I need the
worksheet to automatically filter out the rows that do not match the list-box
value.

Appreciate the help!
John

 

[Per Jessen]

Hi John

Place this event code in the code sheet for the desired sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$1" Then
Range("F4:F200").AutoFilter Field:=1, Criteria1:=Target.Value,
Operator:=xlAnd
End If
End Sub

Regards,
Per

 

[Brenner]

That works great! One quick question...

If I add the text "All" to the top of the drop-down list - what do I add to
your code to get everything to "redisplay"?

Thanks!
John

 

[Per Jessen]

Hi John

Thanks for your reply.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$1" Then
If Target.Value = "All" Then
Range("F4:F200").AutoFilter
Else
Range("F4:F200").AutoFilter Field:=1, Criteria1:=Target.Value,
Operator:=xlAnd
End If
End If
End Sub

Regards,
Per