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.

Basic syntax

VBA

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

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).

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