VBA FILTER Function

HomeVBA Functions LIST (Category Wise)How to use the VBA FILTER Function (Syntax + Example)

What is VBA FILTER Function

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.

how to use it

To use VBA's FILTER function you need to understand its syntax and arguments:

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 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 use FILTER Function in VBA

To practically understand how to use 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

Below are some important points which you need to take care while using FILTER function in VBA.

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

Related Functions

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.