VBA - drop down list from filter options

[Yugo101]

Hi all,

Basically in column B2 down [b1 header] i have a list of categories. What I want is when I run my macro for it come up with a selection box or drop down menu and let me choose which category I want to filter the data by. I have the code for everything else just this added filter. Current I have the filter typed in the code so only choose x category. Any help would be great

 

[mumps]

See if this helps. Use the InputBox to enter the desired category. Then you can filter by the variable "cat"

Sub Test()
    Dim cat As String
    cat = InputBox("Please enter the filter category.")
    If cat = "" Then Exit Sub
    'your code here to filter using the variable 'cat'
End Sub

 

[Yugo101]

See if this helps. Use the InputBox to enter the desired category. Then you can filter by the variable "cat"

Sub Test()
    Dim cat As String
    cat = InputBox("Please enter the filter category.")
    If cat = "" Then Exit Sub
    'your code here to filter using the variable 'cat'
End Sub

Thanks for that I’ll give it a try.

Is there no way to do like unique values then can choose from drop downlist or anything like that?

 

[mumps]

First of all you would need to create a data validation dropdown list in a cell, let's say cell B1. The drop down list would include all the categories starting in B2 and down the column. To do this, you would click on B1 and create the data validation list using this formula:

=OFFSET($B$2,0,0,COUNTA($B:$B),1)

Next you would copy and paste the macro below into the worksheet code module by right clicking the tab name for your sheet and clicking 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. When you select a category in B1, the macro would run automatically to filter the data.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "B1" Then Exit Sub
    Application.ScreenUpdating = False
    'your code here to filter using the variable 'Target.Value'
    Application.ScreenUpdating = True
End Sub

 

[Yugo101]

Thanks the help got it all sorted

 

[mumps]

You are very welcome.