Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Array Functions – Filter()


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


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

    • 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).



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)

Filter Array -Filter(arrA, "M")

Filter Array -Filter(arrA, “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”

Filter(arrA, "J", False)

Filter(arrA, “J”, False)

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)

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

Filter(arrA, “e”, True, vbTextCompare)


You may also like...

2 Responses

  1. James says:

    Very useful documentation – well displayed…it is refreshing to use a doucmentation page whose descriptions and layout are clear, uncluttered and complete. I commend you and appreciate your post. – James

  2. Pawan Yadav says:

    Very useful knowledge,

    I have one Que: can it will possible that I will get its index number instead of value.
    For example in msgbox instead of “May” can i get 5????

    If yes than please let me know…. 🙂

Leave a Reply

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

%d bloggers like this: