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 DateValue Function

The DateValue function in VBA returns a VBA Date from the given String representation of a date wherein the time information is ignored. It is unable to interpret dates that include the...

1 minute read.

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.

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 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 LCase Function

VBA LCase Function: The LCase function in VBA converts the given String into lower case text. Syntax LCase (String) Parameter String (required)- This parameter represents the text string that you want to convert to...

1 minute read.

VBA Charts Basic Operations

VBA Charts- Basic Operations The Chart Object in Excel VBA represents the collection of all the charts sheet present in a workbook. A chart can be either an embedded chart or a separate chart sheet. The...

6 minutes read.

VBA Regex Pattern Validation

VBA Regex Pattern Validation The key purpose of using Regex in VBA was to validate the data and extract the same category of data. There are certain formats that are standard...

6 minutes read.

Excel VBA CLng Function

VBA CLng Function: The CLng function in VBA converts an expression into a Long data type. It returns a long value ranging between -2,147,483,648 and 2,147,483,647. Syntax CLng (Expression) Parameter Expression (required) – This...

1 minute read.

Looping in VBA

Looping in VBA There are many situations where a programmer needs to execute a block of the repetitive code number of times. Writing the same statement will make the program tedious and monotonous....

3 minutes read.

Userform Events

What are Events? Anything you do in to trigger an excel file is an event (an action). Example: If you want a greeting message ‘Good Day’ whenever an excel file is...

7 minutes read.

Excel VBA Space Function

VBA Space Function: The Space function in VBA creates a String consisting of a specified number of spaces. Syntax Space (Number) Parameter Number (required) - This parameter represents the number of spaces. Return This function returns a...

1 minute read.

Excel VBA Oct Function

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

1 minute read.

Excel VBA Second Function

Excel VBA Second Function: The Second function in VBA returns the second element for the specified time.  Syntax Second (Time) Parameter Time (required) – This parameter represents the time. Return This function returns the second...

1 minute read.

Excel VBA StrConv Function

The StrConv function in VBA converts a string into a specified format. Syntax StrConv (String, Conversion, [LocaleID]) Parameter String (required) – This parameter represents the string to be converted. Conversion (required) – This parameter specifies the type of conversion. It can...

1 minute read.

Excel VBA CDec Function

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

1 minute read.

Excel VBA Weekday Function

Excel VBA Weekday Function: The TimeValue function in VBA returns an integer (1 to 7), signifying the day of the week for the specified date. Syntax Weekday (Date, [FirstDayOfWeek]) Parameter Date (required) – This parameter...

2 minutes read.

Excel VBA- Pivot Table Fields

VBA- Pivot Table Fields: The Pivot Fields collection contains all the fields from the data source, including any calculated fields. The main aspect of adding a field is its Position...

4 minutes 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.

Excel VBA LTrim Function

VBA LTrim Function: The LTrim function in VBA removes the leading spaces from a supplied text string. Syntax LTrim (String) Parameter String (required) - This parameter represents he text string that you want to remove...

1 minute read.

Excel VBA DateSerial Function

The DateSerial function in VBA returns a Date from a supplied year, month, and day number. Syntax DateSerial (Year, Month, Day) Parameter Year (required) – This parameter represents an integer signifying the year. Month (required) – This parameter...

2 minutes read.