Excel VBA Array Filter: Methods, Code, and Examples

To filter a one-dimensional array of strings in Excel VBA, use the built-in Filter function, for example Filter(names, "Ava", True, vbTextCompare). It returns the elements containing the search text; for numeric arrays or two-dimensional worksheet data, use a loop to select the values or rows you need.

What “array filter” means in VBA

“Excel VBA array filter” can refer to two different tasks: finding matching items in an array, or filtering worksheet records that have been loaded into an array. VBA’s Filter function handles the first task, but it is deliberately limited. It searches a one-dimensional array of strings for a text fragment and returns a new array containing the matches.

There is no general-purpose VBA function named ArrayFilter that works like a database query across numbers, columns, and conditions. For those jobs, a macro typically loops through the array, tests each value or row, and builds a result. This distinction matters because a worksheet range containing several columns becomes a two-dimensional array when read into VBA, which is not a valid source for the built-in Filter function.

Filtering is also different from sorting. A filter keeps selected values or rows and leaves out the rest; it does not put the results in alphabetical or numerical order unless you add a separate sorting step.

Using the built-in Filter function

Filter takes a source array, a search string, and optional settings for whether to include matches and how to compare text. The default is to include matching elements and use a binary comparison, which is case-sensitive. Set the comparison argument to vbTextCompare when you want a case-insensitive text search.

Sub FindNamesContainingAva()
    Dim names As Variant
    Dim matches As Variant
    Dim i As Long

    ' Array contains strings; Filter searches for the text fragment.
    names = Array("Ava", "Mia", "Ava Chen", "Noah")
    matches = Filter(names, "Ava", True, vbTextCompare)

    ' This example has matches, so the returned array has bounds.
    For i = LBound(matches) To UBound(matches)
        Debug.Print matches(i)
    Next i
End Sub

Run the procedure from the Visual Basic Editor and view its results in the Immediate window. With these sample values, it prints Ava and Ava Chen. The search is for a substring, not an exact whole-item match: a search for Ava also finds Ava Chen. To test exact equality, use a loop and compare each element to the full target value instead.

The returned array is zero-based, so use LBound and UBound rather than assuming a particular starting index. If no elements match, Filter returns an empty array. Code should account for that before trying to loop over its bounds; for workflows where no-match cases are common, a custom loop that counts matches or handles the empty result explicitly is often easier to control.

Filtering rows from a worksheet array

For a table with multiple columns, read the range into a Variant array and test the relevant field in each row. The following example assumes that the worksheet named Data has data, without headers, in A2:D100. The third column within that range is the region, and the macro copies rows whose region is West to F2 and across. It assumes those cells in the region column do not contain Excel error values.

Sub CopyWestRows()
    Dim ws As Worksheet
    Dim data As Variant
    Dim result As Variant
    Dim r As Long
    Dim c As Long
    Dim matchCount As Long
    Dim rowOut As Long
    Dim rowCount As Long
    Dim colCount As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    data = ws.Range("A2:D100").Value2
    rowCount = UBound(data, 1)
    colCount = UBound(data, 2)

    ' First pass counts matching rows so the result can be sized once.
    For r = 1 To rowCount
        If StrComp(CStr(data(r, 3)), "West", vbTextCompare) = 0 Then
            matchCount = matchCount + 1
        End If
    Next r

    If matchCount = 0 Then
        MsgBox "No West rows were found."
        Exit Sub
    End If

    ReDim result(1 To matchCount, 1 To colCount)
    rowOut = 0

    ' Second pass copies every field from each matching row.
    For r = 1 To rowCount
        If StrComp(CStr(data(r, 3)), "West", vbTextCompare) = 0 Then
            rowOut = rowOut + 1
            For c = 1 To colCount
                result(rowOut, c) = data(r, c)
            Next c
        End If
    Next r

    ' Writes the selected records beginning in column F, row 2.
    ws.Cells(2, 6).Resize(matchCount, colCount).Value = result
End Sub

The two passes make the output dimensions known before values are copied. That avoids repeatedly expanding an array while searching. The example preserves the original row order and copies all four fields, not just the region value. It does not add headings or clear old output below the new results: add headings separately if needed, and choose an output area that will not leave confusing stale records after a later run returns fewer rows.

Building a filter for other data types

For numbers, exact comparisons, multiple conditions, or entire records, write the condition you actually need in a loop. A common pattern is to count qualifying elements, allocate a result array of the right size, and then make a second pass to copy them. For a small array, a temporary array followed by a resize may be adequate, but repeated resizing can be inefficient. In VBA, ReDim Preserve can preserve existing values only when changing the last dimension of a multidimensional array, so growing a two-dimensional row-and-column result one row at a time is awkward.

Decide how comparisons should behave before implementing them. For text, StrComp with vbTextCompare provides a case-insensitive comparison; vbBinaryCompare is case-sensitive. Numeric values should generally be compared as numbers rather than converted to text. If the data can contain empty cells, error values, or mixed types, handle those cases deliberately: converting an error value with CStr, for example, can raise a type mismatch.

For a single text fragment in a one-dimensional string array, use Filter.
For exact matches, numeric tests, or several conditions, use a loop with an explicit test.
For a multi-column table, test a field in each row and copy the full row when it qualifies.
For repeated processing of large data, read the worksheet range into memory once rather than reading individual cells inside every loop.

VBA filtering compared with Excel’s FILTER formula

If the goal is to show a live filtered result on a worksheet, a formula may be simpler than a macro. In Microsoft 365 or Excel 2021 and later, the worksheet FILTER function returns matching rows as a spilling array. For data in A2:D100 with regions in C2:C100, enter this in an empty cell:

=FILTER(A2:D100,C2:C100="West","No matches")

That formula returns the rows whose region is West, or the text “No matches” if none qualify. It recalculates as source values change. VBA is more useful when the process must copy results to a particular location, perform additional actions, work with arrays in memory, or run as part of a larger macro. A worksheet formula is not available in older Excel versions that lack the dynamic-array FILTER function; a VBA loop or Excel’s built-in table filtering can be alternatives.

Excel’s table filters are another option when a person needs to inspect or temporarily hide records rather than create a separate output array. They are interactive, while a VBA procedure can automate a repeatable workflow. These approaches solve related but not identical problems, so choose based on whether the result needs to update automatically, be copied, or be controlled by a user.

Common problems and how to avoid them

A frequent error is passing a multi-column range array directly to VBA’s Filter function. A range such as A2:D100 becomes a two-dimensional array, but Filter expects a one-dimensional array of strings. Extract the relevant column into a one-dimensional array or, more commonly, loop through the rows and copy complete matches.

Other problems often come from assumptions about bounds and data:

Case-sensitive results: the default comparison is binary. Pass vbTextCompare for case-insensitive matching.
Unexpected partial matches: Filter looks for a substring. Use an equality test in a loop for exact matches.
No matches: an empty result has no ordinary elements to iterate. Handle this outcome before accessing bounds or writing a result.
One-cell ranges: reading a multi-cell range returns a two-dimensional array, but a single cell’s Value2 is a scalar. Code intended for arbitrary range sizes needs to account for that difference.
Wrong column number: array column indexes are relative to the loaded range. In A2:D100, column 3 means worksheet column C; in C2:F100, column 3 means worksheet column E.
Unexpected output leftovers: a macro that writes fewer rows than last time does not automatically erase the old remaining values. Clear only a deliberately chosen output area if that behavior is wanted.

Running and protecting a VBA macro

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste a procedure into the module. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. Run the macro from the editor or through Developer > Macros. On a Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; the keyboard shortcut may depend on the keyboard and function-key settings, so use the menu if Option+F11 does not open it.

Save a copy of the workbook before running a macro that changes cells. Macro actions cannot be reversed with Ctrl+Z, and a procedure may overwrite output if its target range is not chosen carefully. Save a workbook containing VBA in a macro-enabled format such as .xlsm. Do not enable macros in an unfamiliar workbook unless you trust its source; macros can make changes beyond the visible worksheet.

Frequently Asked Questions

Does VBA have an ArrayFilter function?

No general-purpose built-in function named ArrayFilter is provided by VBA. VBA does have Filter for finding text fragments in a one-dimensional string array. For numeric arrays or rows with multiple fields, write a loop or use another suitable Excel feature.

Is VBA Filter case-sensitive?

By default, the comparison is binary and case-sensitive. Supply vbTextCompare as the comparison argument to make a text search case-insensitive. The setting changes how text is compared; it does not turn substring matching into exact matching.

Can Filter search a two-dimensional array?

No. The built-in function is designed for a one-dimensional array of strings, not a two-dimensional array read from a multi-column range. Loop over the rows, test the desired column, and copy each matching row to a result array.

How do I filter an array for an exact match?

Use a loop and compare each element with the complete target value, rather than using Filter, which searches for a substring. Choose case-sensitive or case-insensitive comparison intentionally, and check that values have the expected data type.

Should I use VBA or the Excel FILTER formula?

Use the worksheet formula when a live, automatically recalculating result is the goal and the Excel version supports dynamic arrays. Use VBA when the result needs to be copied or processed as part of an automated procedure. For simple interactive filtering, a table’s built-in filter may be more convenient than either.

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.