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:
- 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.
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.
About the Author
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 ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.