VBA Filter Arrays

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on August 11, 2022

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

vba filter array 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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples