Filter a Range of Data with a Command Button based on a Multi Select ListBox VBA

[Heatherisace]

Hello,

I am trying to filter a range of data based on the values a user may select from a Multi Select Listbox, but I cannot get it to work.

I have a working Listbox (Listbox1)
My command button (CommandButton1) does not execute the filter, I have searched and searched, and tried many different codes, but I just can't seem to figure it out.

Here is an example of my Table:

			Open	Open	Closed	Closed
Area	Region	Territory	Product X	Product Y	Product X	Product Y
A	1	Joe	1	6	3	8
A	1	Jan	2	4	1	5
A	2	Jim	4	6	2	4
B	3	Jack	1	5	1	2
B	4	Jill	3	1	3	4
B	4	Jon	2	5	7	3
C	5	Jeff	1	2	5	4
C	6	Judy	5	3	3	1

<tbody>
</tbody>

So if this were my table, say on a sheet called "Master", I would want my filter to start in Row 2, and the values in the listbox are values in column A (A, B, and C in this example) of which any combination of could be selected.

Please help, I am stuck

 

[dmt32]

Hi welcome to the board.

A simple way to start is turn the macro recorder on & then do your filter. As an example you would get:

Sub Macro1()
    Selection.AutoFilter Field:=1, Criteria1:="A"
    Selection.AutoFilter Field:=3, Criteria1:="Jim"
End Sub

You can then use this to develop your project further.
If still in need of assistance, post all code have - many here to give support & guidance.

Dave

 

[Heatherisace]

Hi,

Thank you for your response. I cannot use the recorder because it does not recognize a selection checked in the listbox.

Here is what I have so far. The 2 problems with it are that it does not recognize multiple listbox selections, and that it filters out the header line, so in the above example, if I tried to filter column A for Area "B", it would also remove Row 2 (the header row).

Private Sub CommandButton1_Click()


    Application.ScreenUpdating = False
    Dim dict As Object, i
    Set dict = CreateObject("Scripting.Dictionary")
    dict.RemoveAll
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) = True Then dict.Add Me.ListBox1.List(i), ""
    Next
    Const delim = "^^^"
    Dim arr
    If UBound(dict.keys) = -1 Then
        Rows("1:" & Rows.Count).EntireRow.Hidden = False
    Else
        arr = Join(dict.keys, delim)
        arr = arr & delim
        For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            Rows(i).Hidden = Not InStr(arr, Cells(i, 1) & delim) > 0
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

Any additional help someone could give would be much appreciated.

Thanks,
Heather