VBA FILTER Function (Syntax + Example)

The VBA FILTER function is listed under the array category of VBA functions. When you use it in a VBA code, it can return strings from an array of strings based on the string you have specified as a subset. In simple words, it can specify a string and it will look for all those values where that string is a part of the main string.

Syntax

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

Arguments

• SourceArray: The array with strings that want to filter.
• Match: The string you want to filter in the SourceArray.
• [Include]: This is a Boolean to define if the weather to filter value which includes the Match, or doesn’t include [This is an optional argument and if omitted VBA takes TRUE by default].
• [Compare]: A string value to define the comparison to make while filtering the array [This is an optional argument and if omitted VBA takes vbBinaryCompare by default].
  • vbBinaryCompare: For binary comparison.
  • vbTextCompare: For text comparison.
  • vbDatabaseCompare: For Database Comparison.

Example

To practically understand how to use the VBA FILTER function, you need to go through the below example where we have written a vba code by using it:

Sub example_FILTER()
Dim nameAry As Variant
Dim myAry(0 To 4) As String
myAry(0) = Range("A1").Value
myAry(1) = Range("A2").Value
myAry(2) = Range("A3").Value
myAry(3) = Range("A4").Value
myAry(4) = Range("A5").Value
nameAry = Filter(myAry, "Sh")
End Sub

In the above code, we have used FILTER to get the value from the array (myAry has values from the cells we have defined) that includes “Sh” in it, and now, “nameAry” includes the “Jay Sh” and “Peter Sh” as both have “Sh” in it.

Notes

• If the source array which you have supplied is NULL then VBA will return an error.
• The array returned by the filter will always be a one-dimensional and zero-based array.