Using Autofilter in VBA (Part 2)

In this article, Mytour will present you with some examples of how to use Autofilter in VBA. Additionally, readers can explore more articles on Mytour to learn about functions like TRIM and DIR in VBA.

1. Example of Autofilter in VBA

Here are some examples of how to use Autofilter in VBA:

1.1 Example 1: Filtering data based on Text condition

Suppose you have a data file like the one below and want to filter data based on the Item column:

This code snippet will filter all rows where the entries are Printer:

Sub FilterRows()

Worksheets('Sheet1').Range('A1').AutoFilter Field:=2, Criteria1:='Printer'

End Sub

The code above references Worksheet 1, and within the worksheet, it refers to cell A1 (which is a cell in the dataset).

Note that in this example, we use Field:=2 because the Item column is the 2nd column in the dataset, counting from the left.

1.2 Example 2: Filtering data based on multiple criteria (AND / OR) in the same column

Suppose we have the same dataset and want to filter all records where the items are either Printer or Projector:

To achieve this, we will use the following code snippet:

Sub FilterRowsOR()

Worksheets('Sheet1').Range('A1').AutoFilter Field:=2, Criteria1:='Printer', Operator:=xlOr, Criteria2:='Projector'

End Sub

Note that in the code snippet, we utilize the xlOR operator. This allows VBA to use both criteria and filter data if either of the two criteria is met.

Similarly, we can also employ the AND criteria.

For instance, if we want to filter all records with a quantity greater than 10 but less than 20, we can use the following code:

Sub FilterRowsAND()

Worksheets('Sheet1').Range('A1').AutoFilter Field:=4, Criteria1:='>10', _

Operator:=xlAnd, Criteria2:='<>

End Sub

1.3 Example 3: Filtering data based on multiple criteria across different columns

Suppose we have the following data file:

With Autofilter, we can filter multiple columns simultaneously.

For instance, if we want to filter all records where Printer and Sales Rep are both Mark, we can use the following code:

Sub FilterRows()

Using Worksheets('Sheet1').Range('A1')

.AutoFilter field:=2, Criteria1:='Printer'

.AutoFilter field:=3, Criteria1:='Mark'

End Using

End Sub

1.4 Example 4: Using Autofilter to Filter Top 10 Data

Suppose we have the following data table:

Use the following code to filter the top 10 records (based on the Quantity column):

Sub FilterTopRecords()

Assuming the worksheet is named ActiveSheet, use the following code to filter the top 10 records (based on the Quantity column):

End Sub

In the above example, the worksheet is named ActiveSheet. You can replace it with your own worksheet name.

Note that in the above example, to retrieve the top 5 items, simply change the number in Criteria1:='10'' from 10 to 5.

Use the following code to fetch the top 5 items:

Sub FilterTop5Records()

ActiveSheet.Range('A1').AutoFilter Field:=4, Criteria1:='5', Operator:=xlTop10Items

End Sub

Regardless of the desired top items count, the Operator value always remains xlTop10Items.

Similarly, to fetch the bottom 10 items, use the following code:

Sub FilterBottom10Records()

ActiveSheet.Range('A1').AutoFilter Field:=4, Criteria1:='10', Operator:=xlBottom10Items

End Sub

1.5 Example 5: Filtering 10% of Data Using AutoFilter

We continue using the data table from the above example.

Use the following code to retrieve the top 10% of records (based on the Quantity column):

Sub FilterRowsTop10Percent()

ActiveSheet.Range('A1').AutoFilter Field:=4, Criteria1:='10', Operator:=xlTop10Percent

End Sub

Since our dataset has 20 records, the above code will return the top 2 records (10% of the total).

1.6 Example 6: Using Wildcards in Autofilter

Consider the dataset below:

To filter all rows with item names containing the term 'Board,' use the following code:

Sub FilterRowsWildcard()

Worksheets('Sheet1').Range('A1').AutoFilter Field:=2, Criteria1:='*Board*'

End Sub

In the above code, we use the wildcard character * (asterisk) before and after the term 'Board' (as the criteria).

A wildcard can represent any number of characters. Therefore, the above code will filter any item containing the word 'Board'.

1.7 Example 7: Copying filtered rows to a new worksheet

To filter records based on criteria and copy the filtered rows, we can use the following macro. The macro will copy the filtered rows, create a new worksheet, and then paste the filtered rows into the new worksheet:

Sub CopyFilteredRows()

Declare a variable 'rng' as Range

Declare a variable 'ws' as Worksheet

Check if AutoFilter is not applied on 'Sheet1'

Display a message box 'No rows filtered' if AutoFilter is not applied

Exit the Subroutine

End the If statement

Set the variable 'rng' to the filtered range of 'Sheet1'

Create a new worksheet and set the variable 'ws' to the new worksheet

Copy the range 'rng' and paste it starting from cell A1

End the Subroutine

The provided code checks if there are any filtered rows in Sheet1. If no rows are filtered, it displays a message box.

If filtered rows exist, it copies those rows, inserts a new worksheet, and pastes the filtered rows into the newly inserted worksheet.

1.8 Example 8: Copy Filtered Columns to a New Worksheet

If you want to filter records based on criteria and copy the filtered columns, you can use the following macro. This macro copies the filtered columns, adds a new worksheet, and then pastes these columns into the newly added worksheet:

Sub CopyFilteredColumns()

Declare a variable 'rng' as Range

Declare a variable 'ws' as Worksheet

If Worksheets('Sheet1').AutoFilterMode = False Then

MsgBox 'no column is filtered'

Exit Sub

End If

Set rng = Worksheets('Sheet1').AutoFilter.Range

Set ws = Worksheets.Add

rng.Copy Range('A1')

End Sub

The code above checks whether any column is filtered in Sheet1. If no columns are filtered, it displays a message box.

If columns are filtered, it copies those columns, inserts a new worksheet, and pastes the filtered columns into the newly inserted worksheet.

1.9 Example 9: Filter data based on cell values

By utilizing Autofilter in VBA along with dropdown menus, we can create a function where selecting an item from the menu will filter all records related to that item.

This structural approach can be handy when needing to swiftly filter data, subsequently using it for other tasks.

To achieve this, we use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If the selected target address is '$B$2',

If the value in cell B2 is 'All',

Autofilter on Range('A5'),

Otherwise,

Autofilter on Range('A5') based on the value in cell B2,

End If

End If

End Sub

It's a worksheet event code, executed on changes in the worksheet, specifically targeting cell B2 (where the dropdown menu resides).

Additionally, an If-Then-Else condition is used to check if the user has selected All from the menu. If All is chosen, the entire dataset will be displayed.

Note that this code should not be placed in a module. Instead, it should be placed in the backend of the worksheet containing this data.

Follow the steps below to insert the code in the worksheet code window:

Step 1: Open the VB Editor (using the shortcut Alt + F11).

Step 2: In the Project Explorer, double-click on the name of the worksheet where you want to apply this filtering function.

Step 3: In the worksheet code window, copy and paste the above code.

Step 4: Close the VB Editor.

From now on, using the dropdown menu will automatically filter the data.

This article, Using Autofilter in VBA (Part 2), has just presented you with some examples of utilizing Autofilter in VBA. If you have any doubts or questions, readers can leave their opinions in the comments section below the article.