VBA Array AutoFilter: Filter Data in Memory or Excel

Excel’s VBA AutoFilter method filters a worksheet range or table; it does not filter a VBA array directly. To filter an array, loop through its rows and copy the matches into a second array, or write the data to a worksheet range and apply AutoFilter there.

Which approach is best depends on what you need next: worksheet filters are convenient when people need to inspect or change the results, while an in-memory filter can be faster and tidier when VBA will process the matching records without displaying them.

What “AutoFilter on an array” means

A VBA array is a block of values held in memory. A range, by contrast, is a group of cells in a worksheet. Although you can load a range into an array with one assignment, the array is not itself a range and has no filter drop-downs, hidden rows, or worksheet address. Calling AutoFilter on an array variable is therefore not a supported way to filter it.

This distinction causes a common confusion: assigning Range.Value to a Variant gives you an array of the cells’ values, but it does not preserve the worksheet range as a filterable object. The array is useful for fast VBA operations; the original cells remain available as a range if you want Excel’s built-in filtering behavior.

In most ordinary range-to-array assignments, a multi-cell range becomes a two-dimensional array. Its first dimension represents rows and its second represents columns. If the source range is A1:D100, for example, column A is array column 1, column B is 2, and column C is 3. Those array positions are not the worksheet’s absolute column numbers.

Apply AutoFilter to worksheet data

Use worksheet AutoFilter when the data should stay in Excel and users need to see which rows match. The following Windows VBA example assumes a worksheet named Data, a contiguous table-like block beginning at A1, headers in row 1, and a status field in column C. The field number is 3 because it is counted from the left edge of the filtered range, A.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z. This example applies a filter and can hide rows that do not match.

Option Explicit

Sub FilterWorksheetRows()
    Dim ws As Worksheet
    Dim dataRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1").CurrentRegion

    ' Field 3 is column C relative to the range starting at A1.
    dataRange.AutoFilter Field:=3, Criteria1:="Open"
End Sub

CurrentRegion selects the connected block around A1, so blank rows or blank columns inside the data can split the region. If the data is an Excel Table, using its known range is often clearer. AutoFilter changes which worksheet rows are visible; it does not create a separate array of the visible records.

On Windows, the range-based method shown above is commonly used. Microsoft documents the Range.AutoFilter method as unsupported in Excel for Mac; Mac VBA has related Selection- and ListObject-based methods, so adapt and verify the call for the target version rather than assuming this exact range call will work. Excel for the web does not run VBA macros. Its worksheet filter controls are separate from VBA.

Filter an array in memory

For an in-memory result, use ordinary VBA logic: count matching rows, allocate an output array of the required size, then copy the header and matching records. This two-pass method avoids trying to resize a two-dimensional array in a way VBA does not support. The sample assumes Data!A1:D100 contains headers in row 1 and records beneath them, with status in column C. It sends the result to F1:I and treats “Open” as a case-insensitive text match.

Before running this macro, save a copy of the workbook. The macro writes results beginning in F1, so make sure that output area is clear and does not contain information you need.

Option Explicit

Sub FilterArrayInMemory()
    Dim ws As Worksheet
    Dim source As Variant
    Dim result As Variant
    Dim rowIndex As Long
    Dim colIndex As Long
    Dim matchCount As Long
    Dim outputRow As Long
    Dim statusColumn As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    source = ws.Range("A1:D100").Value2
    statusColumn = 3

    ' Count matching data rows; row 1 contains the headers.
    For rowIndex = 2 To UBound(source, 1)
        If StrComp(CStr(source(rowIndex, statusColumn)), _
                   "Open", vbTextCompare) = 0 Then
            matchCount = matchCount + 1
        End If
    Next rowIndex

    ' Include one output row for the headers, even with no matches.
    ReDim result(1 To matchCount + 1, 1 To UBound(source, 2))

    For colIndex = 1 To UBound(source, 2)
        result(1, colIndex) = source(1, colIndex)
    Next colIndex

    outputRow = 2
    For rowIndex = 2 To UBound(source, 1)
        If StrComp(CStr(source(rowIndex, statusColumn)), _
                   "Open", vbTextCompare) = 0 Then
            For colIndex = 1 To UBound(source, 2)
                result(outputRow, colIndex) = source(rowIndex, colIndex)
            Next colIndex
            outputRow = outputRow + 1
        End If
    Next rowIndex

    ws.Range("F1").Resize(UBound(result, 1), _
        UBound(result, 2)).Value = result
End Sub

The returned array contains the original headers followed by the matching rows, in their original order. If there are no matches, it still contains the header row. The code assumes the status cells contain text or values that can be converted to text; an Excel error value in that column can make CStr fail. If errors are possible, test IsError before converting a cell value.

Understand the array’s shape and indexes

For an array loaded from a multi-cell range, use UBound(source, 1) to get the last row index and UBound(source, 2) to get the last column index. In the example, source(rowIndex, 3) reads the third column of the loaded block—worksheet column C because the block starts at A. If the source range instead starts at C1, its third array column corresponds to worksheet column E.

Headers need special handling because they are not normally part of the filter criteria. The sample begins matching at array row 2, copies row 1 once, then writes matching records after it. If your source array has no header row, start both loops at row 1 and omit the header-copying code. If the source range has a different number of columns or a different status-column position, update the range and the statusColumn value together.

A range containing just one cell can behave differently from a multi-cell range: its Value2 result is a scalar rather than a two-dimensional array. The sample deliberately uses a multi-cell range. For reusable code that may receive a single cell, add logic to normalize that scalar into a two-dimensional array before calling UBound.

Choose criteria that match your data

The in-memory example uses StrComp with vbTextCompare, which is suitable for a simple text category such as Open. It does not reproduce every AutoFilter criterion. AutoFilter supports criteria such as comparison operators, wildcards, and multiple conditions; an array loop must implement whichever rules the task requires.

For example, a numeric threshold in an array loop should compare numeric values as numbers, not as text. Check that the value is numeric before using a conversion such as CDbl, particularly if cells may contain blanks or errors. For multiple conditions, combine Boolean tests with And or Or. Write down whether the conditions mean “both must match” or “either may match” before coding, because that choice changes the result.

AutoFilter criteria also have details worth noting. A field number is relative to the filtered range, and the range should include a header row for the usual drop-down behavior. Text comparisons in a worksheet filter are not the same thing as VBA’s default binary string comparison. If case sensitivity matters, define it deliberately in the array logic rather than assuming the two methods behave identically.

Decide between a worksheet filter and an array

Use a worksheet filter when people need to review records, select criteria interactively, or work with the remaining visible rows in place. It is also a natural fit when the source is already a worksheet table and preserving its layout matters. Keep in mind that filtering hides nonmatching rows rather than deleting them; clearing the filter makes them visible again.

Use an array when VBA needs to process many rows repeatedly, when you want an independent result without changing source visibility, or when the result will feed another calculation or export. Reading and writing a block through arrays can reduce repeated cell-by-cell worksheet access. The trade-off is that your code must define the matching rules, handle types and errors, allocate output, and decide where the result goes.

There is also a hybrid approach: read a range into an array, process it in memory, and write the finished array to a separate worksheet area. The sample does exactly that. It leaves the input data’s visibility alone, but the output is still a normal range that a user can sort, filter, or format afterward.

Common problems and fixes

Wrong field number: AutoFilter’s Field is counted from the left edge of the filtered range, not from worksheet column A. If the range begins at C, its first field is C.
Blank rows inside CurrentRegion: CurrentRegion may stop at a blank row or column. Use a defined range or a well-formed Excel Table if the data is not one uninterrupted block.
Only one row returned or no array bounds: Check that the source is a multi-cell range and that the loop starts after the header. A one-cell range may return a scalar rather than a two-dimensional array.
Output overwrites existing cells: Choose a destination outside the source and other content, or clear and validate a dedicated output area before writing. Do not assume the destination is empty.
Unexpected matches: Check for leading or trailing spaces, inconsistent data types, blanks, and error values. An exact text comparison will not automatically trim spaces or treat numbers stored as text as numeric values.
Macro appears not to undo: That is expected. Ctrl+Z cannot reverse VBA changes, which is why a saved copy is important before running code that writes or changes filters.

Alternatives: FILTER formulas and tables

If the goal is a visible, automatically updating result rather than VBA automation, Excel’s FILTER worksheet function may be simpler. With headers in A1:D1, records in A2:D6, status values in C2:C6, and Open records in rows 2 and 4, enter this in F2: =FILTER(A2:D6,C2:C6="Open","No matches"). It spills the two matching records into the cells below and beside F2. FILTER is available in Microsoft 365 and Excel 2021 or later; older versions may not support it.

An Excel Table is another useful alternative when the data is meant to remain on a worksheet. Tables provide built-in filter arrows and expand as rows are added. They do not turn an array in memory into a filterable object, however. The choice is still between filtering worksheet data, applying a worksheet formula to return a filtered result, and writing VBA logic to filter the array itself.

Frequently Asked Questions

Can I use AutoFilter directly on a VBA array?
No. AutoFilter applies to worksheet ranges or supported table/selection objects, not directly to an in-memory array. Loop through the array and build a result array, or place the values in worksheet cells and filter that range.

Does AutoFilter delete the rows that do not match?
No. It hides nonmatching worksheet rows. Clearing the filter displays them again. If you need a separate collection of matches, copy visible records to another range or filter the values in a VBA array.

Can I filter a two-dimensional array by more than one column?
Yes. Add tests for each relevant array column in the loop. Use And when every condition must be true, or Or when any condition can qualify a row. Decide how to handle blanks, errors, and text-versus-number values.

Why does Field:=3 filter the wrong worksheet column?
The field number is relative to the leftmost column of the range passed to AutoFilter. It means the third column in that range, not necessarily worksheet column C. Check the range’s starting column and count from there.

Can I use VBA AutoFilter in Excel for the web or on Mac?
Excel for the web does not run VBA. On Mac, the Range.AutoFilter method shown in the Windows example has a documented limitation; use the filter controls or an appropriate supported Selection- or ListObject-based VBA approach for the Excel version in use.

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.