How to use Filter function in VBA?

In VBA for Microsoft Excel, if you want to filter an array of strings based on a particular substring, you can use the `Filter` function. The `Filter` function returns a zero-based array containing all the elements of an existing array that match a specified filter string.

Here is how to use the `Filter` function:

   Filter(SourceArray, Match, Include, [Compare])

Function Parameters

SourceArray: The existing one-dimensional array of strings that you want to filter.
Match: The substring you want to match in the array elements.
Include: A Boolean value indicating whether to include (`True`) or exclude (`False`) the array elements that match the filter string.
Compare: Optional. A numeric value specifying the type of comparison to use when comparing array elements with the filter string. `0` for a binary comparison (case-sensitive) or `1` for a textual comparison (case-insensitive).

Syntax
Example Usage

Here’s a simple example showing how to use the `Filter` function in VBA:

Sub FilterExample()
    Dim arr() As String
    Dim result() As String
    
    ' An array of strings
    arr = Array("apple", "banana", "cherry", "date", "fig", "grape")
    
    ' Using the Filter function to find elements that contain "ap"
    result = Filter(arr, "ap", True) ' Include elements that contain "ap"
    
    ' Print the results to the Immediate Window (Ctrl+G to view)
    Dim i As Integer
    For i = LBound(result) To UBound(result)
        Debug.Print result(i)
    Next i
End Sub

After running this code, the Immediate Window would display the following output:

apple
grape

This output includes the elements from the array `arr` where the substring “ap” appears.

Note: The `Filter` function is not designed for filtering tables or ranges in Excel worksheets. To filter data in tables or ranges, you would need to use Excel’s AutoFilter method or Advanced Filter in conjunction with VBA.

facebooklinkedin