Autofilter using array, includes (Blanks) in the results (and I dont want it to)

[grabla]

I have an autofilter, based on a string array.
When I apply the autofilter, (blanks) is always ticked and therefore blanks are returned in the results

    Dim i, irow As Integer
    Dim Criteria_Val(1 To 50) As String
    Dim rg As Range

    For i = 0 To UserFormDottedTypes.ListBoxDottedTypes.ListCount - 1
        If UserFormDottedTypes.ListBoxDottedTypes.Selected(i) = True Then
            irow = irow + 1
            Criteria_Val(irow + 1) = UserFormDottedTypes.ListBoxDottedTypes.list(i)
            Debug.Print irow, UserFormDottedTypes.ListBoxDottedTypes.list(i), Len(UserFormDottedTypes.ListBoxDottedTypes.list(i))
        End If
    Next i

    Set rg = Range("A16")           'Header label for column A
    Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))  'All the data in column A
    
    rg.AutoFilter Field:=1, Criteria1:=Array(Criteria_Val), Operator:=xlFilterValues

^^^ The code reads the selected items from a list box, and saves those selected items in to the criterial_val string array. I am diming the string array (1 to 50) because it could hold up to 50 selections from the list box. I've tried (0 to 50) and using criteria_val(0) but that makes no difference to the blanks shown in the results.


During testing, If I place any amount of the listbox values held by the array, manually in to the autofilter, it doesnt include the blanks in the results :D

rg.AutoFilter Field:=1, Criteria1:=Array(".always w7",".deployed"), Operator:=xlFilterValues

But when those two values are added to the criterial_val string array in positions 1 and 2, the blanks are shows in the results.
I've tried setting all values (1 to 50) of criteria_val to vbnull, but again, no difference.

Any ideas? This seems a common question, but I've yet to find the answer. Where is the (blanks) option coming from to make the autofilter show blanks in the results?

 

[grabla]

Hmm, a little bit more testing this morning and it looks like its the unused criteria_val values in the array that are causing the blanks to appear.

So I dim the string (1 to 50), and only use first 'n' in the array, but the whole array (all fifty values) are passed to the autofilter, including the unused ones.
So I'll see if setting the unused values to "Foo" fixes the issue.
Either that or find a way of dimensioning the array dynamically to the maximum number of values that it needs to hold (an no more)

 

[grabla]

Yep, found it :D

Set all the values in the string array to "FOO", then set the first n values to what you actually want to filter against, and it works :D No more blanks.