VBA Filter Function

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

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


The one dimensional array which you want to filter.

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

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.

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

Constant Value Description
vbUseCompareOption -1 Uses option compare
vbBinaryCompare 0 Binary comparison (distinguishes letter case)
vbTextCompare 1 Textual comparison (ignores letter case)

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.

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:

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.

Simply the best place to learn Excel VBA