Excel 2013: VBA Code to filter worksheet data from multiple listbox selections

Question

I've spent 3 days now searching for a solution and I know I'm close, but I just don't get my issue and why it is happening.

First of all, I have a spreadsheet which contains staff names (column A, starting from row 5) and resource planning data (abbreviations of projects) from column B to HG. Each coloumn -except for column A- represents 1 day of a calendar (column headers are dates).

I also have a user form containing 3 listboxes (multiselect). LB1 = staff names, LB2 = project abbreviations, LB3 don't matter for now. I also have 3 buttons on this user form, 1 to reset the LB selections, 1 to apply the filter to the spreadsheet and 1 to reset the filter on the spreadsheet.

My code for resetting the LB selections and the filter on the spreadsheet works just fine. The one to apply the filter just won't work the way it is supposed to. The code for this button so far looks as follows (just trying to handle 1 LB for now):

' Apply filter to spreadsheet
Private Sub CB_FilterActive_Click()
    Dim arrMitarbeiter() As Variant
    Dim i As Integer, count As Integer

    count = 1
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then
            ReDim Preserve arrMitarbeiter(count)
            arrMitarbeiter(count) = ListBox1.List(i)
            count = count + 1
        End If
    Next i
    Worksheets("Einsatzplan").UsedRange.Cells.AutoFilter field:=1, Criteria1:=Array(arrMitarbeiter)
End Sub

Here's the thing:

hitting the "apply filter button" makes all rows containing data in the spreadsheet dissapear. When I try to debug the code, I see that the array for the autofilter gets populated correctly in regards to LB selections. When I hit the dropdown of the filter applied on the worksheet and go to "textfilter -> equals" and have a look at the populated filter criteria, it's right there. It just won't show the respective rows. I tried a lot of stuff and I just don't know where the problem is. Additionaly, I'm just a VBA beginner trying to figure things out. So any help would be much appreciated (as well for the case when I want to combine selections of all 3 listboxes to hand it to the autofilter)!

Sincerely, moshpit

EDIT:

This is what my current code looks like, rewrote it to be sure about the algorithm. I also debugged the whole thing. Funny thing is: during debugging (while 1 item in the listbox1 is selected), the array contains this exact value. After applying the filter and going to the filter options dropdown -> textfilter -> equals, there is no value in there, which makes me assume that this is why it hides all rows. But how come the value is in the array and doesn't get applied to the filter afterwards? Also, Field:= should be an optional parameter regarding to the Microsoft documentation, but when I leave that out, it gives me a runtime error (error# 1004: Can't execute AutoFilter method of range object).

Option Explicit

' Apply Filter to Sheet
Private Sub CommandButton2_Click()
    Dim x() As String, r() As String, k() As String
    Dim i As Integer, j As Integer, s As Integer

    ReDim x(0)

    Application.ScreenUpdating = False
    ActiveSheet.UsedRange.AutoFilter

    ' Filter Array for ListBox1
    For i = 0 To ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) = True Then
            x(UBound(x)) = Me.ListBox1.List(i)
            ReDim Preserve x(UBound(x) + 1)
        End If
    Next i
    If UBound(x) <> 0 Then
        Worksheets("Tabelle1").Range("A1").AutoFilter Field:=1, Criteria1:=x, Operator:=xlFilterValues
        ReDim Preserve x(UBound(x) - 1)
    End If

    ReDim r(0)

    ' Filter Array for ListBox2
    For j = 0 To ListBox2.ListCount - 1
        If Me.ListBox2.Selected(j) = True Then
            r(UBound(r)) = Me.ListBox2.List(j)
            ReDim Preserve r(UBound(r) + 1)
        End If
    Next j
    If UBound(r) <> 0 Then
        ReDim Preserve r(UBound(r) - 1)
        Worksheets("Tabelle1").Range("B1 : HG1").AutoFilter , Criteria1:=r, Operator:=xlFilterValues
    End If

    ReDim k(0)

    ' Filter Array for ListBox3
    For s = 0 To ListBox3.ListCount - 1
        If Me.ListBox3.Selected(s) = True Then
            k(UBound(k)) = Me.ListBox3.List(s)
            ReDim Preserve k(UBound(k) + 1)
        End If
    Next s
    If UBound(k) <> 0 Then
        ReDim Preserve k(UBound(k) - 1)
        Worksheets("Tabelle1").AutoFilter , Criteria1:=k, Operator:=xlFilterValues
    End If

    Application.ScreenUpdating = True

End Sub

' Reset Filter Mask
Private Sub CommandButton1_Click()
    Dim iCount1 As Integer
    Dim iCount2 As Integer
    Dim iCount3 As Integer

    For iCount1 = 0 To Me!ListBox1.ListCount - 1
        Me!ListBox1.Selected(iCount1) = False
    Next iCount1

    For iCount2 = 0 To Me!ListBox2.ListCount - 1
        Me!ListBox2.Selected(iCount2) = False
    Next iCount2

    For iCount3 = 0 To Me!ListBox3.ListCount - 1
        Me!ListBox3.Selected(iCount3) = False
    Next iCount3
End Sub

' Delete Filter from Sheet
Private Sub CommandButton3_Click()
    On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

Answer 1

There are two problems in there:

1 - arrMitarbeiter is already an array as you defined it in Dim arrMitarbeiter() As Variant

So, you cannot pass Array(arrMitarbeiter) to the filter, pass only arrMitarbeiter instead.

2 - If you dont use the xlFilterValues operator, it will filter only the last item of the array, so add this operator.

Fix just this line (i made it two lines just for reading):

Worksheets("Einsatzplan").UsedRange.Cells.AutoFilter 
     field:=1, Criteria1:=arrMitarbeiter, Operator:=xlFilterValues