VBA - Concatenated String in Autofilter Array not working

[naj90]

Running the below code does not filter the actual column with Autofilter. Is there any reason why?

Sub TestAutoFilter()
    'Filter based on column Importance.
    'PURPOSE: Dynamically Create Array Variable based on a Given Size
    
    Dim myArray()   As Variant
    Dim DataRange   As Range
    Dim cell        As Range
    Dim x           As Long  
    
    'Determine the data you want stored
    Set DataRange = ActiveSheet.UsedRange
    
    'Resize Array prior to loading data
    
    'Loop through each cell in Range and store value in Array
    counter = 0
    For Each chkbx In ThisWorkbook.Sheets("Sheet1").CheckBoxes
        
        If chkbx.Value > 0 And InStr(1, LCase(chkbx.Name), "check") <> 0 Then
            counter = counter + 1
        End If
    Next chkbx
    
    ReDim myArray(counter)
    For Each chkbox In ThisWorkbook.Sheets("Sheet1").CheckBoxes
        If chkbox.Value > 0 And InStr(1, LCase(chkbox.Name), "check") <> 0 Then
            myArray(x) = chkbox.Caption
            x = x + 1
        End If
    Next chkbox
    
    myArrayString = ""
    'Print values to Immediate Window (Ctrl + G to view)
    For x = LBound(myArray) To counter
        myArrayString = myArrayString & "," & myArray(x)
    Next x
    'Debug.Print (myArrayString)
    
    myRightString = Left(myArrayString, Len(myArrayString) - 1)
    myLeftString = Right(myRightString, Len(myRightString) - 1)
    'Debug.Print (RTrim(myLeftString))
    ThisWorkbook.Sheets("Data").Range("A3:F3").AutoFilter Field:=3, _
                             Criteria1:=Array(myLeftString), _
                             Operator:=xlFilterValues
    
End Sub

I also tried to change the last bit to:

Criteria1:=myArray

But nothing happens, it just keeps returning an empty filter.

 

[RoryA]

It won't work with your concatenated string, but should work with myarray instead. If it doesn't I'd guess nothing actually matches the captions exactly - check for leading/trailing spaces for example.

 

[naj90]

It won't work with your concatenated string, but should work with myarray instead. If it doesn't I'd guess nothing actually matches the captions exactly - check for leading/trailing spaces for example.

Thanks RoryA. Yeah, I had tried it before and it didn't work. But in subsequent tries it worked with myarray.. I have no clue why :/. But I'll leave the code here for reference for other people.