[Excel VBA] How to Extract Data Using the FILTER Function into an Array

Traditionally, extracting specific rows of data based on criteria involved using AutoFilter with copy-paste or looping through rows one by one.

However, in Excel for Microsoft 365 and Excel 2021 or later, the powerful dynamic array function called FILTER was introduced. By using this function from VBA, you can extract data simply and very quickly.

This article explains how to use WorksheetFunction.Filter in VBA to get extraction results as an array and write them to a sheet all at once.

Important: Required Environment

To use this technique, your Excel version must support the FILTER function (Microsoft 365 or Excel 2021 and later).

Also, since the FILTER function works very well with Excel Tables, it is highly recommended to convert your source data into a Table (Ctrl+T) beforehand.

Complete VBA Code

The following code extracts all records where the “Representative” column is “Johnson” from a table named “SalesTable” and writes the results starting at cell F2.

Sub ExtractDataWithFilterFunction()

    ' Declare variables
    Dim sourceTable As Range
    Dim criteriaColumn As Range
    Dim criteria As String
    Dim filteredData As Variant

    '--- Settings ---
    ' The entire table containing the data
    Set sourceTable = ThisWorkbook.Worksheets("SalesData").ListObjects("SalesTable").Range
    ' The column to check conditions against ("Representative" column)
    Set criteriaColumn = ThisWorkbook.Worksheets("SalesData").ListObjects("SalesTable").ListColumns("Representative").DataBodyRange
    ' Extraction criteria
    criteria = "Johnson"
    '--- End Settings ---

    ' --- 1. Extract data using the FILTER function and store it in an array ---
    On Error Resume Next ' Prevent error if no data matches
    filteredData = WorksheetFunction.Filter(sourceTable, criteriaColumn = criteria, "No data found")
    On Error GoTo 0
    
    ' Check if an error occurred or if the result is only the "No data found" string (not an array)
    If IsEmpty(filteredData) Or Not IsArray(filteredData) Then
        MsgBox "No data found matching '" & criteria & "'.", vbInformation
        Exit Sub
    End If
    
    ' --- 2. Write the result array to the specified cell at once ---
    Dim outputCell As Range
    Set outputCell = ThisWorkbook.Worksheets("Summary").Range("F2")
    
    ' Expand the destination range to match the size of the array using Resize
    outputCell.Resize(UBound(filteredData, 1), UBound(filteredData, 2)).Value = filteredData

    MsgBox "Data extraction complete."
    
End Sub

Key Points of the Code

1. WorksheetFunction.Filter

filteredData = WorksheetFunction.Filter(sourceTable, criteriaColumn = criteria, "No data found")

We call the FILTER function from VBA. The arguments are as follows:

• Arg 1 (array): The array or entire cell range you want to filter (sourceTable).
• Arg 2 (include): A condition that returns TRUE or FALSE (criteriaColumn = criteria). The function checks if each cell in this column matches the criteria.
• Arg 3 [if_empty] (Optional): The value to return if no results are found ("No data found"). Specifying this makes it easier to handle cases with zero results.

The return value of this function is a 2-dimensional array containing the extracted data. Therefore, the variable receiving it (filteredData) must be declared as a Variant.

2. Writing the Array to the Sheet

outputCell.Resize(UBound(filteredData, 1), UBound(filteredData, 2)).Value = filteredData

This is a standard technique for writing a VBA array to a cell range in one go.

• UBound(filteredData, 1): Gets the number of rows (height) of the array.
• UBound(filteredData, 2): Gets the number of columns (width) of the array.
• .Resize(...): Expands the outputCell (F2) to a range that fits the row and column count of the array.
• .Value = filteredData: By assigning the array directly to the .Value property of the expanded range, all data is written to the sheet instantly. This is significantly faster than writing cells one by one in a loop.

Summary

In modern Excel environments where the FILTER function is available, data extraction via VBA changes dramatically.

1. Use WorksheetFunction.Filter to get matching data as an array.
2. Check the size of the acquired array using UBound.
3. Adjust the output range using .Resize to match the array size.
4. Assign the array to .Value to write everything at once.

This new approach makes your code much shorter and improves processing speed compared to traditional AutoFilter or looping methods. If you are using Excel 365 or 2021, please try this powerful technique.