Excel VBA Filter Function
2020-02-16 10:25:15

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
VBA Filter Function

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
VBA Filter Function