Excel VBA AutoFilter Array

Question

I'm working with AutoFilters in VBA with Microsoft Excel. I'm having an issue with how it handles arrays for filtering; I've watered down my original context to a simplified version (originally in hopes of understanding the issue):

In a worksheet in Range A1:A5, let's say we have Fruit, Apple, Banana, Orange, and Pear, respectively. An AutoFilter has been applied such that Fruit is a column header.

Running the below code returns the expected results (Apple, Banana, and Orange but not Pear):

Range("A1").Select
ActiveSheet.Range("A1:A5").AutoFilter Field:=1, _
    Criteria1:=Array("=*an*", "=*app*"), Operator:=xlFilterValues

In the project I'm working on, the filter criteria are passed in as String variables, which works as the above. The issue is that not every criterion is applied every time, so some of them should have no effect.

For example:

Dim A As String, B As String, C As String
A = "=*an*"
B = Empty
C = "=*ap*"

Range("A1").Select
ActiveSheet.Range("A1:A5").AutoFilter Field:=1, _
    Criteria1:=Array(A, B, C), Operator:=xlFilterValues

With B thrown into the mix, filtering returns no records (whether it's left null, set to Empty, or uses wildcards like =*). Replacing B with Empty (hard-coded) in the actual criteria array returns the expected result, however.

I've used similar code in the past (and had it work), albeit with an AutoFilter that was part of a ListObject. At this point, the only thing I can think of is concatenating the filters into a String with delimiters and splitting it into an Array variable (so that it's the precise size, since an un-set item in the collection messes that up the same way a standard variable does). But that seems unintuitive and cumbersome.

Am I missing something obvious here?

David Zemens David Zemens · Accepted Answer · 2013-09-06T03:35:19

I can't think of a way to do this that doesn't involve testing for empty parameters, here is one approach that might work for you, and also prevents duplicate expressions.

Sub Main()
    Dim a As String
    Dim B As String
    Dim C As String
    Dim filterCriteria as Variant
    a = "=*an*"
    B = Empty
    C = "=*ap*"
    filterCriteria = CombineArrays(Array(a, B, C))

    If Not uBound(filterCriteria) = -1 Then 

        Range("A1").Select
        ActiveSheet.Range("A1:A5").AutoFilter Field:=1, _
            Criteria1:=filterCriteria, Operator:=xlFilterValues

    End If
End Sub

Function CombineArrays(arr As Variant) As Variant
    Dim a As Variant
    Dim filterDic As Object 'Scripting.Dictionary
    Set filterDic = CreateObject("Scripting.Dictionary")

    For Each a In arr
        If Not filterDic.Exists(a) And Not a = vbNullString Then
            filterDic.Add a, a
        End If
    Next

    CombineArrays = filterDic.Keys

    Set filterDic = Nothing
End Function

Excel VBA AutoFilter Array

1 Answers