VBA Filter in Excel — Subset an Array in One Call, and Why It Is Not AutoFilter

TL;DR — Filter(sourceArray, match) returns a new, zero-based array containing only the elements that contain the substring match. It is a search over an array in memory, not a sheet — nothing to do with AutoFilter. Three things bite: it matches a substring (so "AB" keeps "CAB"), it is case-sensitive by default (pass vbTextCompare to ignore case), and a no-match result is an empty array with UBound = -1, not an error. The optional third argument Include flips it: Filter(a, "x", False) keeps everything that does not contain "x".

Sub FilterDemo()
    Dim names As Variant, hits As Variant
    names = Array("Smith", "Jones", "Smithson", "Adams")

    hits = Filter(names, "Smith")          ' keep elements containing "Smith"
    Debug.Print hits(0)                     ' Smith      <- index 0, always zero-based
    Debug.Print hits(1)                     ' Smithson   <- substring match, not whole-word
    Debug.Print UBound(hits)                ' 1          <- two hits: indexes 0 and 1

    hits = Filter(names, "smith")           ' lowercase -> NO match by default
    Debug.Print UBound(hits)                ' -1         <- empty array, not an error
End Sub

The full signature, including the two arguments that trip people up:

Filter(sourceArray, match, [include], [compare])
'      1-D array     text    True/False  vbTextCompare = ignore case

What you'll learn

• The mental model — Filter searches an array and hands back the keepers
• Why Filter is not AutoFilter, and when you actually want each
• The substring trap: Filter matches inside words, not whole values
• Case sensitivity, and the vbTextCompare fix
• The empty-result trap (UBound = -1) and how to guard it
• Include:=False to invert the filter, and the round-trip with Split and Join

The mental model: Filter searches an array, not a sheet

Filter is the array cousin of InStr. Where InStr asks "is this text inside one string, and where?", Filter asks "which of these strings contain this text?" and hands you back a brand-new array holding only the ones that do. You give it a list; it gives you the shorter list.

That framing tells you exactly when to reach for it: you already have a one-dimensional string array in memory — the pieces from a Split, the keys from a Dictionary, a list of file names from Dir — and you want the subset that mentions something. It is a one-line replacement for the loop everyone writes:

' The long way everyone writes first
Dim i As Long, n As Long
ReDim keep(UBound(names)) As String
For i = 0 To UBound(names)
    If InStr(1, names(i), "Smith") > 0 Then
        keep(n) = names(i): n = n + 1
    End If
Next i
ReDim Preserve keep(n - 1)

' The same thing, in one call
keep = Filter(names, "Smith")

The confusion that has to go: Filter is not AutoFilter

This is the single biggest source of "why won't Filter work on my sheet" questions, and the answer is that you are holding two unrelated tools that happen to share a word. They are not variants of each other:

If your data is in cells and you want to narrow it down, you want AutoFilter or, for multi-criteria copies, Advanced Filter — not this function. Filter() earns its place after you have pulled data into an array. A common, correct pipeline is: read a column, flatten it to a 1-D array (a job for Transpose), then Filter it in memory without disturbing the sheet at all.

Trap 1: Filter matches a substring, not a whole value

Filter keeps any element where the match text appears anywhere inside it. That is a feature for "contains" searches and a footgun for "equals" searches:

Dim codes As Variant, r As Variant
codes = Array("AB", "ABC", "CAB", "XY")
r = Filter(codes, "AB")
' r holds "AB", "ABC", AND "CAB" — every element that CONTAINS "AB"

If you wanted only the elements equal to "AB", Filter is the wrong tool — it cannot do exact match. For an exact-match membership test, reach for a Dictionary (dict.Exists(key)) or Application.Match. Use Filter when "contains" is genuinely what you mean.

Trap 2: Filter is case-sensitive by default

Out of the box, Filter uses a binary compare — "smith" does not match "Smith". This is the opposite of most people's mental default and silently returns an empty array. The fourth argument fixes it:

r = Filter(names, "smith")                        ' binary -> misses "Smith"
r = Filter(names, "smith", True, vbTextCompare)   ' text -> matches "Smith", "SMITH", "smith"

Note you must supply the third argument (Include, here True) to reach the fourth. As an alternative, Option Compare Text at the top of the module makes every string comparison in that module case-insensitive, Filter included — handy, but it changes behaviour module-wide, so pass vbTextCompare per-call when you want the effect local.

Trap 3: no match returns an empty array, and Include inverts

A Filter that matches nothing does not raise an error — it returns a valid array of length zero, whose UBound is -1. A For i = 0 To UBound(r) loop over it harmlessly does nothing, but reaching for r(0) blows up with subscript out of range. Guard on UBound before you index:

r = Filter(names, "Zzz")
If UBound(r) < 0 Then
    MsgBox "No matches."
Else
    MsgBox (UBound(r) + 1) & " match(es) found."   ' count = UBound + 1
End If

And the argument almost nobody remembers: Include. It defaults to True (keep the matches). Pass False and Filter inverts — it keeps every element that does not contain the match. This is how you strip items out of a list in one line:

' Drop every path that mentions "temp"
clean = Filter(paths, "temp", False)

The opinion: Filter is for arrays you already hold — and the Split-Filter-Join line

Filter is superb glue and a poor engine. It shines on data that is already a 1-D string array and where "contains" is the real question — trimming a folder listing, narrowing tokens from a Split, keeping the error lines out of a log array. It is the wrong answer the moment you want exact matches (use a Dictionary), the moment your data is still in cells (use AutoFilter), or the moment the elements are not strings (Filter coerces to string and compares text, which quietly misleads on numbers).

Its best trick is being the middle of a three-function pipeline. Split parses, Filter keeps, Join reassembles — one expression that replaces a fifteen-line loop:

' From one CSV line, keep only the fields mentioning "2026", rejoin them
result = Join(Filter(Split(line, ","), "2026"), ", ")

That line is the whole cluster in miniature: Split turns the string into an array, Filter subsets it, Join collapses it back — no loop, no index arithmetic, no off-by-one.

When the array wrangling outweighs the point — describe the job instead

Filtering is rarely the goal. The goal is "pull the overdue invoices out of this export, drop the test rows, and give me the client codes." By the time you have read the column into an array, flattened it, guarded the case sensitivity, handled the empty result, and rejoined the survivors, the plumbing dwarfs the one answer you wanted. ExcelMaster lets you state that goal in plain English — "list the client codes for every overdue, non-test invoice" — and it generates Python that reads the data, applies the real match logic, and backs up your file first — you describe the result you want, and it handles the case sensitivity, the empty cases, and the edges.

Frequently asked questions

What does the Filter function do in VBA?

Filter(sourceArray, match) returns a new array containing only the elements of sourceArray that contain the substring match. The source must be a one-dimensional array, and the result is always zero-based. It searches an array in memory and returns a smaller array — it does not change any cells.

Is VBA Filter the same as AutoFilter?

No. Filter() is a function that subsets a one-dimensional array in memory and returns a new array. AutoFilter is a worksheet method that hides rows in the visible sheet based on criteria. They share a name and nothing else — if your data is in cells, you want AutoFilter; if it is already an array, you want Filter().

Why does VBA Filter return nothing when the text clearly matches?

Almost always case sensitivity. Filter uses a binary compare by default, so "smith" does not match "Smith". Pass vbTextCompare as the fourth argument — Filter(a, "smith", True, vbTextCompare) — or put Option Compare Text at the top of the module.

How do I exclude items with VBA Filter?

Pass False as the third argument, Include. Filter(paths, "temp", False) returns every element that does not contain "temp". With Include omitted or True, Filter keeps the matches; with False it keeps the non-matches.

How do I check if VBA Filter found any matches?

Test UBound of the result. A no-match Filter returns an empty array whose UBound is -1, not an error, so If UBound(result) < 0 Then means nothing matched. The number of matches is UBound(result) + 1, since the array is zero-based.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-03.

Related guides: VBA Join · VBA Transpose · VBA Split · VBA AutoFilter · VBA Dictionary