VBA Array Filter Function

VBA Array Filter function

VBA Array Filter Function

The filter function returns an array, which contains subset of string based on specified criteria.

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

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 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 substrings which are not containing specified filter substring.

'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.

Instructions to use Macro

Here are the instructions to use above macro in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • You can see output on the screen
  • Find above specified output screenshot.

Other related VBA Arrays articles

You may also like the related VBA Array articles.

Back to VBA Array Functions Back to VBA Arrays

Leave a Comment

Your email address will not be published. Required fields are marked *