The Excel VBA Filter function returns an array containing a subset of values that contain (or don’t) a substring against a provided array (Variant).

VBA Filter Function Syntax

Filter( sourcearray, match, [ include, [ compare ]] )

Parameters

sourcearray
The one dimensional array which you want to filter.

match
A string you want to find across all items in the array.

include
Optional. If True the Filter function will return a subset array that contain the match string. If False the Filter function will return a subset array that do not contain the match string. The default value is True.

compare
Optional. The type of comparison to be performed. vbBinaryCompare is the default value. VBA Constant values:

Get all matches in a VBA Array

We will start with some simple examples. The code below will be used in most examples and only the VBA Filter function code line will change so I will omit the VBA For loop in other examples.

Dim arr As Variant, filterArr As Variant, aVal As Variant
arr = Array("Dragon", "Dog", "DRAGONfly", "Cat", "fly")
    
filterArr = Filter(arr, "Dragon")

'Print the contents of the filtered Array
For Each aVal In filterArr 
    Debug.Print aVal
Next aVal
'Result: Dragon

Because the default values of the optional parameters are True for include and vbBinaryCompare for compare the Filter function returned a subset array including only the item which matched the exact lettercase for the word Dragon.

In above example I also used the VBA Array function to quickly define a VBA Array of strings.

Matches in a VBA Array w/o letter case

To get all matches regardless of the lettercase change the compare parameter to vbTextCompare:

Dim arr As Variant, filterArr As Variant
arr = Array("Dragon", "Dog", "DRAGONfly", "Cat", "fly")
    
filterArr = Filter(arr, "Dragon", Compare:=vbTextCompare) 'Result: "Dragon", "DRAGONfly"

Get all items that DO NOT match

The VBA Filter function can be also used to get a subset array that does not contain the match string.

Dim arr As Variant, filterArr As Variant
arr = Array("Dragon", "Dog", "DRAGONfly", "Cat", "fly")
    
filterArr = Filter(arr, "Dragon", False, vbTextCompare) 'Result: "Dog", "Cat", "fly"