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()

Description: 

 Fil­ter() Func­tion returns one dimen­sional array con­tain­ing the fil­tered array ele­ments based upon the fil­ter options provided

Format:

Fil­ter(arrArrayName,FilterValue [, Include[, vbCompare]])

Arguments:
    • arrAr­ray­Name
      • Manda­tory
      • Type: Array
      • Array which needs to be filtered

  •  FilterValue
    • Manda­tory
    • The expres­sion based on which the Array will be filtered
  • Include
    • Optional
    • Type : Boolean
    • Decides whether to include(True) or exclude(False) the Fil­ter­Value. 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).

 

Exam­ple:

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”)   

Fil­ter all the ele­ments of arrA and return array con­sist­ing ele­ments which con­tains “M” (Upper Case M)

Filter Array -Filter(arrA, "M")

Fil­ter 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)

Fil­ter all the ele­ments of arrA and return array con­sist­ing ele­ments which doesn’t con­tain “J”

Filter(arrA, "J", False)

Filter(arrA, “J”, False)

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

Fil­ter all the ele­ments of arrA and return array con­sist­ing ele­ments which con­tain “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 use­ful doc­u­men­ta­tion — well displayed…it is refresh­ing to use a douc­men­ta­tion page whose descrip­tions and lay­out are clear, unclut­tered and com­plete. I com­mend you and appre­ci­ate your post. — James

  2. Pawan Yadav says:

    Very use­ful knowledge,

    I have one Que: can it will pos­si­ble that I will get its index num­ber instead of value.
    For exam­ple in msg­box 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: