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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Open[/TD]
[TD]Open[/TD]
[TD]Closed[/TD]
[TD]Closed[/TD]
[/TR]
[TR]
[TD]Area[/TD]
[TD]Region[/TD]
[TD]Territory[/TD]
[TD]Product X[/TD]
[TD]Product Y[/TD]
[TD]Product X[/TD]
[TD]Product Y[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]Joe[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]Jan[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[TD]Jim[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[TD]Jack[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]Jill[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[TD]Jon[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]5[/TD]
[TD]Jeff[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]6[/TD]
[TD]Judy[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

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