VBA-Excel: Array Functions – Filter()

Description: 

 Filter() Function returns one dimensional array containing the filtered array elements based upon the filter options provided

Format:

Filter(arrArrayName,FilterValue [, Include[, vbCompare]])

Arguments:

• arrArrayName
  • Manda­tory
  • Type: Array
  • Array which needs to be filtered

•  FilterValue

• Manda­tory
• The expression based on which the Array will be filtered
• Include
  • Optional
  • Type : Boolean
  • Decides whether to include(True) or exclude(False) the FilterValue. Default is True that means it will include the FilterValue.
• vbCom­pare
  • Optional
  • Type: Numeric
  • The type of com­par­i­son to find the string in the main string, like vbBina­ryCompare (Value =0), vbTextCom­pare (value=1).

Example:

Function FnFilterArray()

    Dim arrA

  arrA = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

  arrTemp = Filter(arrA, "M")    

  MsgBox Join(arrTemp, "*")    

  arrTemp1 = Filter(arrA, "J", False)

  MsgBox Join(arrTemp1, "*")

  arrTemp1 = Filter(arrA, "e", True, vbTextCompare)    

  MsgBox Join(arrTemp1, "*")

End Function

Cases :

1) Filter(arrA, "M")   

Filter all the elements of arrA and return array consisting elements which contains “M” (Upper Case M)

Note: Filter(arrA, "M")    and Filter(arrA, "M", True)    will return the same result because if True is the default value if not provided.

2) Filter(arrA, "J", False)

Filter all the elements of arrA and return array consisting elements which doesn’t contain “J”

3) Filter(arrA, "e", True, vbTextCompare)   

Filter all the elements of arrA and return array consisting elements which contain “e” (Upper as well as lower cases)

Also Read:

1. VBA-Excel : Strings Functions – Instr
2. VBA Excel - Cells, Ranges and Offset: Refer Range by using A1 Notations
3. VBA-Excel: String Functions – String()
4. VBA-Excel: String Functions – Len()
5. Excel-VBA : Range - Lock/Unlock Specific Cells