Excel VBA Filter Function
VBA Filter Function: The Filter function in VBA returns a subset for the given string array, based on specified criteria.
Syntax
Filter (SourceArray, Match, [Include], [Compare])
Parameter
SourceArray (required) – This parameter the array of Strings that you want to filter.
Match (required) – This parameter the string that you want to search for within each element of the supplied SourceArray.
Include (optional) – This parameter represents the Boolean argument that specifies whether the returns array should consist of elements that include or do not include the supplied Match String. By default, this parameter is set to True.
It can take the following values:
True (default)- It returns values that include the Match String
False – It returns values that do not include the Match String
Compare (optional) – This parameter represents the type of String to make the comparison. By default, this parameter is set to vbBinaryCompare.
It can take the following values:
vbBinaryCompare (default value)– It performs a binary comparison
vbTextCompare – It performs a text comparison
vbDatabaseCompare – It performs a database comparison
Return
This function returns a subset for the given string array, based on specified criteria.
Example 1
Sub FilterFunction_Example1() ' Filtering the given array of city for values that contain "Mumbai". ' Initializing the array with the cities values. Dim city As Variant Dim i As Integer city = Array("Mumbai", "Delhi", "Bangalore", "Faridabad", "Gurugram", "Mumbai") ' Applying the Filter function to fetch city containing "Mumbai". Dim smithNames As Variant MumbaiCity = Filter(city, "Mumbai") For i = 0 To 5 Cells(i + 2, 1).Value = city(i) Next 'for filtered city For i = 0 To 1 Cells (i + 2, 2).Value = MumbaiCity(i) Next End Sub
Output
Array | Filtered Array |
Mumbai | Mumbai |
Delhi | Mumbai |
Bangalore | |
Faridabad | |
Gurugram | |
Mumbai |
Example 2
Sub FilterFunction_Example2() ' Filtering the given array of city for values that do not contain "Mumbai". ' Initializing the array with city. Dim city As Variant Dim i As Integer city = Array("Mumbai", "Delhi", "Bangalore", "Faridabad", "Gurugram", "Mumbai") ' Applying the Filter function to fetch all the cities except "Mumbai". Dim smithNames As Variant MumbaiCity = Filter(city, "Mumbai", False) For i = 0 To 5 Cells(i + 2, 1).Value = city(i) Next 'for filtered city For i = 0 To 3 Cells(i + 2, 2).Value = MumbaiCity(i) Next End Sub
Output
Name | Sales Amount |
Mumbai | Delhi |
Delhi | Bangalore |
Bangalore | Faridabad |
Faridabad | Gurugram |
Gurugram | |
Mumbai |