VBA Array Filter Function in Excel

VBA Array Filter Function in Excel. The filter function returns an array, which contains subset of string based on specified criteria.

Table of Contents:

Objective
Syntax of VBA Filter Function in Excel
Includes all filtered strings – case sensitive
Extract all filtered strings – not a case sensitive
Excludes or doesn’t contain filtered string – Case Sensitive
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax of VBA Array Filter Function

Here is the Syntax of the Filter Function in Excel VBA.

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

Where SourceArray: Required parameter. The array of strings to be searched. It shouldn’t be null. If it is null then returns an error.
Match: Required parameter. The string to search for.
Include: An optional Boolean parameter. It represents to include or exclude the matching string.
Compare: An optional parameter. It represents type of comparison(Binary or Textual or Database). Default value is ‘0’ i.e vbBinaryCompare.

VBA Constant	Value	Explanation
vbUseCompareOption	-1	Performs a comparison using the ‘option compare’
vbBinaryCompare	0	Performs a Binary comparison
vbTextCompare	1	Performs a Textual comparison
vbDatabaseCompare	2	Microsoft Access only. Performs a comparison based on information in your database.

Includes all filtered strings – case sensitive

Let us see the example vba macro code using array filter function in Excel. In the below example we have specified an array with values. We are filtering or extracting substrings values which are case sensitive.

'Case: Case Sensitive and includes all filtered data
Sub VBA_Array_Filter_Function_Ex1()

    'Variable declaration
    Dim myArray As Variant
    Dim SubStringArray As Variant
    Dim FilterValue As Variant
    
    'Create an Array
    myArray = Array("Sunday", "MonDay", "Tuesday", "WednesDay", "Thursday", "FriDay", "Saturday")
    
    'Etract string contains 'Day' from specified array
    SubStringArray = Filter(myArray, "day")
    'or
    'SubStringArray = Filter(myArray, "day", True)
    'or
    'SubStringArray = Filter(myArray, "day", True,vbBinaryCompare)
    
    'Loop through array values
    For Each FilterValue In SubStringArray
        Debug.Print FilterValue
    Next

End Sub

Here is the screenshot of above vba macro code.

Extract all filtered strings – not a case sensitive

'Case: Ignores Case Sensitive while filtering data
Sub VBA_Array_Filter_Function_Ex2()

    'Variable declaration
    Dim myArray As Variant
    Dim SubStringArray As Variant
    Dim FilterValue As Variant
    
    'Create an Array
    myArray = Array("Sunday", "MonDay", "Tuesday", "WednesDay", "Thursday", "FriDay", "Saturday")
    
    'Ignores Case Sensitive while filtering data
    SubStringArray = Filter(myArray, "day", True, vbTextCompare)
    
    'Loop through array values
    For Each FilterValue In SubStringArray
        Debug.Print FilterValue
    Next

End Sub

Here is the screenshot of above vba macro code.

Excludes or doesn’t contain filtered string – Case Sensitive

Let us see the example vba macro code using array filter function in Excel. In the below example we have specified an array with values. We are filtering or extracting sub-strings which are not containing specified filter sub-string.

'Case: Excludes or doesn't contain filtered string and Case Sensitive
Sub VBA_Array_Filter_Function_Ex3()

    'Variable declaration
    Dim myArray As Variant
    Dim SubStringArray As Variant
    Dim FilterValue As Variant
    
    'Create an Array
    myArray = Array("Sunday", "MonDay", "Tuesday", "WednesDay", "Thursday", "FriDay", "Saturday")
    
    'Excludes filtered string which contains 'day' from specified array
    SubStringArray = Filter(myArray, "Day", False)
    'or
    'SubStringArray = Filter(myArray, "day", False, vbBinaryCompare)
    
    'Loop through array values
    For Each FilterValue In SubStringArray
        Debug.Print FilterValue
    Next

End Sub

Here is the screenshot of above vba macro code.