Logo
×

Excel VBA Filter Function

VBA Filter Function: The Filter function in VBA returns a subset for the given string array, based on specified criteria.

Syntax

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

Parameter

SourceArray (required) – This parameter the array of Strings that you want to filter.

Match (required) – This parameter the string that you want to search for within each element of the supplied SourceArray.

Include (optional) – This parameter represents the Boolean argument that specifies whether the returns array should consist of elements that include or do not include the supplied Match String.  By default, this parameter is set to True.

It can take the following values:

True (default)- It returns values that include the Match String

False – It returns values that do not include the Match String

Compare (optional) – This parameter represents the type of String to make the comparison. By default, this parameter is set to vbBinaryCompare.

It can take the following values:

vbBinaryCompare (default value)– It performs a binary comparison

vbTextCompare – It performs a text comparison

vbDatabaseCompare – It performs a database comparison

Return

This function returns a subset for the given string array, based on specified criteria.

Example 1

Sub FilterFunction_Example1()
 ' Filtering the given array of city for values that contain "Mumbai".
 ' Initializing the array with the cities values.
 Dim city As Variant
 Dim i As Integer 
 city = Array("Mumbai", "Delhi", "Bangalore", "Faridabad", "Gurugram", "Mumbai")
 ' Applying the Filter function to fetch city containing "Mumbai".
 Dim smithNames As Variant
 MumbaiCity = Filter(city, "Mumbai")
 For i = 0 To 5
     Cells(i + 2, 1).Value = city(i)
 Next
 'for filtered city
 For i = 0 To 1
     Cells (i + 2, 2).Value = MumbaiCity(i)
 Next
 End Sub

Output

Array Filtered Array
Mumbai Mumbai
Delhi Mumbai
Bangalore
Faridabad
Gurugram
Mumbai
VBA Filter Function

Example 2

Sub FilterFunction_Example2()
 ' Filtering the given array of city for values that do not contain "Mumbai".
 ' Initializing the array with city.
 Dim city As Variant
 Dim i As Integer
 city = Array("Mumbai", "Delhi", "Bangalore", "Faridabad", "Gurugram", "Mumbai")
 ' Applying the Filter function to fetch all the cities except  "Mumbai".
 Dim smithNames As Variant
 MumbaiCity = Filter(city, "Mumbai", False)
 For i = 0 To 5
     Cells(i + 2, 1).Value = city(i)
 Next
 'for filtered city
 For i = 0 To 3
     Cells(i + 2, 2).Value = MumbaiCity(i)
 Next
 End Sub

Output

Name Sales Amount
Mumbai Delhi
Delhi Bangalore
Bangalore Faridabad
Faridabad Gurugram
Gurugram
Mumbai
VBA Filter Function

Related Topics

Excel VBA Round Function

VBA Round Function: The Round function in VBA rounds a number to a specified number of decimal places and returns the number. Syntax Round (Number, [NumDigitsAfterDecimal]) Parameter Number (required) –This parameter represents a numeric value one wants...

1 minute read.

How to save Macro Workbook?

Saving Macro Workbook Default Excel File Extension The default excel file extension is “.xlsx”. But the standard file extension “.xlsx” cannot contains macros. So, the workbook contains macro when save din.xlsx file, all VBA...

1 minute read.

Four VBA Clear methods

Four VBA Clear methods In Microsoft Excel, many times, a situation arises where the user wants to clear the data or any specific range of data. What if the user automates this task with...

6 minutes read.

Excel VBA RTrim Function

VBA RTrim Function: The Rtrim function in VBA removes the leading spaces from the text in the specified string. Syntax RTrim (String) Parameter String (required) – This parameter represents the string from which you want...

1 minute read.

Excel VBA: Select … Case Statement

Select … Case Statement When a group of statements is executed, depending upon the value of an Expression, then Switch Case is used.  If you have several conditions to check, then the If condition...

4 minutes read.

Excel Objects in VBA

What are Excel Objects? The Excel objects belong to the entities that make up an Excel Workbook, Worksheets, Columns, Rows, Cell Ranges, etc. Each object in Excel has loads of Properties that are...

6 minutes read.

VBA Regex

What is a Regex? Regex stands for Regular Expression is basically a pattern matching strings within another string. They are supported in many languages, including .net, C++, Python, etc. They are...

5 minutes read.

VBA Charts

What is a Chart? A chart is used to visually show numbers or data in a spreadsheet (or spread over multiple spreadsheets) so that the end-user can look at the chart...

3 minutes read.

Excel VBA CVar Function

VBA CVar Function: The CVar function in VBA converts an expression into a Variant data type. Syntax CVar (Expression) Parameter Expression (required) – This parameter represents the expression that that you want to convert...

1 minute read.

Excel VBA Hour Function

The hour function in VBA returns the hour element for the specified time. Syntax Hour (Time) Parameter Time (required) – This parameter represents the time. Return This function returns the hour element for the specified time. Example 1 Sub HourFunction_Example1() ...

1 minute read.

VBA Not Equal Operator

What is VBA Not Equal Operator? VBA Not Equal binary operator (“<>”) is a logical function that is used to check if the specified values are not equal or not. This...

5 minutes read.

If ElseIf ElseIf Statement or Nested If statement in VBA

VBA Excel: If … ElseIf … ElseIf Statement or Nested If statement This function enables you to check multiple conditions and, based on that, then run one of the statement blocks present. If...

2 minutes read.

Excel VBA Len Function

VBA Len Function: The Len function in VBA returns the number of characters in a supplied string or the number of bytes required to store a supplied variable. Syntax Len (Expression) Parameter Expression (required)-...

1 minute read.

Excel VBA StrComp Function

VBA StrComp Function: The StrComp function in VBA compares two strings and returns an integer value displaying the result of the comparison. Syntax StrComp (String1, String2, [Compare]) Parameter String1 (required)- This parameter represents the first string to...

2 minutes read.

VBA Creating, Displaying, Uploading UserForms

UserForm is a customized interface and acts as a VBA container and can add various controls as per the required functionality, each of which has certain usage and related properties. You can...

4 minutes read.

Declaring a Variable in VBA

Declaring a Variable A variable is broadly described as a storage location combined with a name and representing a specific value. Declaring a variable is instructing the computer to reserve space...

2 minutes read.

Excel VBA IsArray Function

VBA IsArray Function: The IsArray function in VBA returns a Boolean, showing whether the given variable is an Array or not. Syntax IsArray (VarName) Parameter VarName (required)- This parameter represents the variable that you want...

1 minute read.

Excel VBA Hex Function

VBA Hex Function: The Hex function in VBA converts the given number into hexadecimal notation and returns the result as a string. Syntax Hex (Number) Parameter Number (required) – This parameter represents the numeric value...

1 minute read.

UserForm and its Properties Excel VBA

UserForm and its Properties Userform has certain properties that can be viewed as category wise (based on appearance, behavior, font) or in an alphabetic manner. The property window is used to set or...

11 minutes read.

Excel VBA Date Function

VBA Date Function The Date function in VBA returns the current date. Syntax Date( ) Parameter NA Return This function returns the current date. Example 1 Sub DateFunction_Example1() ' retuning the current date in the variable currentDate Dim currentDate As...

1 minute read.