Excel VBA Filter Function: Syntax, Examples, and Limits

In Excel VBA, the Filter function searches a one-dimensional array of strings and returns the elements that contain a specified piece of text. Use it for a short list of values; to filter complete worksheet rows, use AutoFilter or Excel’s separate worksheet FILTER function instead.

What the VBA Filter function does

Filter is a built-in Visual Basic for Applications function, not a worksheet formula. It takes an array of strings, looks for a match within each string, and returns a new array containing either matching or nonmatching elements. It does not change the source array, sort the results, or apply a filter to worksheet rows.

The match is a substring search. For example, searching for ap can match “Apple” and “Apricot”; it does not require the entire string to equal the search text. The function returns a Variant containing a zero-based array of strings, even if the source array used different index bounds. Use LBound and UBound to loop through results rather than assuming a particular number of matches.

This makes Filter useful when a macro already has a list of text values in memory, such as names, product codes, or items split from a comma-separated string. It is less suitable when the task is to show or hide rows in a table, filter multiple columns, or search numeric and date fields directly.

Syntax and arguments

The syntax is Filter(SourceArray, Match, [Include], [Compare]). The first two arguments are required; the final two are optional.

SourceArray: A one-dimensional array of strings to search. A worksheet range’s Value is commonly a two-dimensional array, so it cannot always be passed straight to Filter.
Match: The text to search for. It can be an empty string, but that may match every string, so handle it deliberately.
Include: A Boolean value. True returns strings that contain the match; False returns strings that do not contain it. If omitted, it defaults to True.
Compare: Controls how text comparisons work. Common choices are vbBinaryCompare for case-sensitive comparison and vbTextCompare for case-insensitive comparison. If omitted, the module’s Option Compare setting determines the comparison; VBA modules normally use binary comparison unless configured otherwise.

For code that should behave consistently regardless of module settings, pass the comparison argument explicitly. vbDatabaseCompare is intended for database comparisons and is generally not the choice for ordinary Excel VBA code.

A simple VBA example

This macro searches five words for the substring “Ap”, ignoring letter case, and prints matching words in the Visual Basic Editor’s Immediate window. Open the editor with Alt+F11 on Windows, insert a standard module, and run the procedure. In Excel for Mac, the Visual Basic Editor is available, though the keyboard shortcut can depend on the keyboard; use the Developer tab’s Visual Basic command if needed.

Sub ShowMatchingWords()
    ' Search a one-dimensional array of strings.
    Dim source As Variant
    Dim matches As Variant
    Dim i As Long
    Dim lastIndex As Long

    source = Split("Almond,Apple,Banana,Apricot,Avocado", ",")
    matches = Filter(source, "Ap", True, vbTextCompare)

    ' UBound can raise an error when there are no matches.
    On Error Resume Next
    lastIndex = UBound(matches)
    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo 0
        Debug.Print "No matches."
        Exit Sub
    End If
    On Error GoTo 0

    For i = LBound(matches) To lastIndex
        Debug.Print matches(i)
    Next i
End Sub

The output is “Apple” and “Apricot”, each on its own line in the Immediate window. Split creates the source array from a delimited string; if the data already exists in an array, use that array instead. The error check matters because a search with no results produces an empty array, and calling UBound on that result can raise an error.

Before running any macro, save a copy of the workbook. VBA actions cannot be reversed with Ctrl+Z, so preserve the original if the macro might change cells or other workbook content.

Using Filter with worksheet data

A common stumbling block is trying to pass a range directly into Filter. A multi-cell range’s Value usually supplies a two-dimensional Variant array, while Filter expects a one-dimensional string array. You can convert the values into a one-dimensional array first, or choose a feature designed to filter worksheet data.

For a small list, a macro can loop through a column, place nonblank cell values into a one-dimensional string array, and then call Filter. Remember to check for error values before converting cell contents with CStr, because attempting to convert an Excel error value can fail. Also decide how blanks should behave: exclude them during the conversion if they are not meaningful list items.

For a table where users need to see matching records and hide the others, Excel’s built-in AutoFilter is usually the better fit. Select a cell in the data, then choose Data > Filter. The column headings receive filter controls, and users can select values or apply text conditions. A VBA macro can also control AutoFilter, but that is a different feature from the VBA Filter function.

If the goal is to return matching rows into another area with a worksheet formula, Microsoft 365 and Excel 2021 or later include the worksheet function FILTER. For example, with names in A2:A20 and departments in B2:B20, this formula returns names whose department is “Sales”: =FILTER(A2:A20,B2:B20="Sales","No matches"). In this example, the returned list spills into cells below the formula. This formula is not VBA code, and it is distinct from the VBA function with the similar name.

Case sensitivity, inclusion, and comparison

By default, the function returns items that include the match, but the optional Boolean argument can reverse that selection. For example, Filter(source, "old", False, vbTextCompare) returns strings that do not contain “old”, without distinguishing uppercase from lowercase. This can be useful for excluding a particular status or removing a known fragment from a list.

With vbBinaryCompare, letter case matters: “Ap” and “ap” are different matches. With vbTextCompare, they are treated as equivalent for the search. Explicitly stating the comparison mode is especially useful when a workbook has several modules, because a module’s Option Compare declaration can otherwise affect the default behavior.

The function does not interpret wildcard characters as Excel criteria. A question mark or asterisk in the match is searched as part of the text, rather than used as a wildcard pattern. It also does not provide operators such as “greater than” or “between” for numeric values. For those needs, use worksheet criteria, a loop with a condition, AutoFilter, or another appropriate approach.

Empty results and common errors

If nothing matches, Filter returns an empty array. A frequent bug is to assume that every result has an element at index zero and immediately read matches(0), or to call UBound(matches) without accounting for the empty case. Either can cause a runtime error. The example above checks UBound with a brief error-handling block before looping.

Other problems often come from the input rather than the search itself:

Wrong array shape: A range’s two-dimensional value array is not the one-dimensional string array expected by Filter.
Non-string values: Convert numbers or other values to text deliberately if textual matching is intended. Check for Excel error values first.
Unexpected case behavior: Pass vbTextCompare or vbBinaryCompare explicitly instead of relying on a module setting.
Unexpected substring matches: Searching for “cat” also matches a longer string such as “catalogue”; the function checks for contained text, not whole-word matches.
Stale results: If a macro writes a shorter result list over a previous output, old values may remain below it. Clear or manage the output area intentionally.

For whole-word matching, a simple substring search is not enough. You would need to define what counts as a word boundary and check each value using additional VBA logic. For exact equality, loop through the array and compare each element to the required value instead of using Filter.

Choosing between Filter, AutoFilter, and FILTER

Choose based on what you need to return and where the data lives. VBA Filter is a compact option for filtering a one-dimensional string array during a macro. AutoFilter is suited to interactive worksheet tables and entire records. The worksheet FILTER function is convenient when a formula should return a live, expanding result range.

Use VBA Filter when your macro needs a subset of text strings in memory and will process those strings in code.
Use AutoFilter when people need to interactively narrow a table and retain the relationship between fields in each row.
Use the worksheet FILTER function when you want matching rows or values to appear in cells and update as the source data changes, provided the Excel version supports dynamic arrays.
Use a loop or another method when criteria involve numbers, dates, multiple conditions, whole words, or more advanced matching rules.

There is no worksheet function called Filter that is interchangeable with VBA’s function. In worksheet formulas, the function name is FILTER; in VBA, the built-in function is Filter. Their purposes overlap, but their inputs, outputs, and places of use differ.

Availability and macro safety

VBA is supported in the desktop versions of Excel for Windows and Mac, but Excel for the web cannot run VBA macros. If a workbook must be used in a browser, use worksheet formulas or another web-compatible workflow instead. The worksheet FILTER function is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not recognize it.

Macros can modify or delete workbook content, and those actions are not undone by Ctrl+Z. Keep a backup before running unfamiliar code, and only enable macros in files from sources you trust. On Windows, use Developer > Visual Basic to edit code or Developer > Macros to run a macro. If the Developer tab is not shown, it can be enabled in Excel Options under Customize Ribbon. Mac menu labels and ribbon layout can differ slightly, but the web version does not provide VBA execution.

Frequently Asked Questions

Is VBA Filter the same as Excel’s FILTER formula?
No. VBA Filter returns selected strings from a one-dimensional array inside a macro. The worksheet FILTER formula returns matching values or rows into worksheet cells and requires a version with dynamic-array support.

Is VBA Filter case-sensitive?
It depends on the comparison mode. Use vbTextCompare for case-insensitive matching or vbBinaryCompare for case-sensitive matching. If you omit the argument, the module’s Option Compare setting determines the comparison.

How do I filter out values containing a word?
Pass False as the third argument. For example, Filter(source, "archived", False, vbTextCompare) returns strings that do not contain “archived”.

Can VBA Filter filter rows in a range?
Not directly. It filters strings in a one-dimensional array, not worksheet records. Use AutoFilter for interactive row filtering, or convert a column to a one-dimensional array if a macro only needs to process text values.

What happens when there are no matching strings?
The result is an empty array. Check for that case before using UBound or reading an element, because those operations can cause a runtime error when the array has no elements.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.