VBA Filter Arrays

The VBA Filter Function allows you to quickly filter arrays. There are several settings to consider when filtering arrays. We will discuss them below.

Filter – Match

By default the VBA Filter Function will filter an array for matches. In the example below we will filter the array for matches with “Smith”.

Sub Filter_Match()

    'Define Array
    Dim strNames As Variant
    strNames = Array("Steve Smith", "Shannon Smith", "Ryan Johnson")

    'Filter Array
    Dim strSubNames As Variant
    strSubNames = Filter(strNames, "Smith")
    
    'Count Filtered Array
    MsgBox "Found " & UBound(strSubNames) - LBound(strSubNames) + 1 & " names."

End Sub

A couple important points:

• The filtered array variable should be declared as data type variant to avoid defining the array size.
• By default, the Filter function is case sensitive. So filtering on “smith” would give a different result than “Smith”. Below we will show you how to change this setting.

Filter – Case Insensitive

By default, VBA is Case Sensitive. This means that for text to match, the cases must be the same (ex. “smith” does not equal “Smith”). This is true of the Filter Function, as well as all (most?) other VBA functions or comparisons.

Personally, I never want VBA to be case sensitive, so I always add Option Compare Text to the top of all of my code modules. Option Compare Text tells VBA to ignore case so that it’s Case Insensitive:

Option Compare Text

Adding Option Compare Text to the top of your module will make the Filter Function case insensitive. Alternatively, you can tell the Filter Function itself to be case insensitive with the vbTextCompare argument:

strSubNames = Filter(strNames, "smith", , vbTextCompare)

Full example:

Sub Filter_MatchCase()

    'Define Array
    Dim strNames As Variant
    strNames = Array("Steve Smith", "Shannon Smith", "Ryan Johnson")

    'Filter Array
    Dim strSubNames As Variant
    strSubNames = Filter(strNames, "smith", , vbTextCompare)
    
    'Count Filtered Array
    MsgBox "Found " & UBound(strSubNames) - LBound(strSubNames) + 1 & " names."

End Sub

Filter – Does Not Match

The Filter Function can also be used to identify array items that DO NOT match the entered criteria by setting the Include argument to FALSE:

strSubNames = Filter(strNames, "Smith", False)

Full Example:

Sub Filter_NoMatch()

    'Define Array
    Dim strNames As Variant
    strNames = Array("Steve Smith", "Shannon Smith", "Ryan Johnson")

    'Filter Array
    Dim strSubNames As Variant
    strSubNames = Filter(strNames, "Smith", False)
    
    'Count Filtered Array
    MsgBox "Found " & UBound(strSubNames) - LBound(strSubNames) + 1 & " names."

End Sub

Filter Function

The VBA Filter function returns an Array subset of a supplied string array.

The Filter Function Syntax is:

Filter( SourceArray, Match, [Include], [Compare] )

The Function arguments are:

• SourceArray – The original Array to filter
• Match – The string to search for
• [Include] – OPTIONAL TRUE (Returns matches), FALSE (Returns elements that do not match)
• [Compare] – OPTIONAL vbBinaryCompare – binary comparison, vbTextCompare – text comparison, vbDatabaseCompare – database comparison