Using Autofilter in VBA (Part 2)

The following article Taimienphi.vn will introduce you some examples of using Autofilter in VBA. In addition, readers can refer to some articles already on Taimienphi.vn to learn more about TRIM, DIR in VBA.

1. Example of Autofilter in VBA

Here are some examples of using Autofilter in VBA:

1.1 Example 1: Filter data based on Text condition

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

The code below will filter all rows, where the entries are Printer:

Sub FilterRows ()

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

End Sub

The above code refers to worksheet 1 and in the spreadsheet it refers to cell A1 (which is the cell in the dataset).

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

1.2 Example 2: Filter data by multiple criteria (AND / OR) in the same column

Suppose we have the same data set and want to filter all records where the entries are Printer or Projector:

To do this, we will use the following code:

Sub FilterRowsOR ()

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

End Sub

Note, in the above code we use the xlOR operator. This allows VBA to use both criteria and filter the data if any one of the two criteria is met.

Similarly, we can also use AND criteria.

For example, if we want to filter all records with numbers greater than 10 but under 20, we can use the code below:

Sub FilterRowsAND ()

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

Operator: = xlAnd, Criteria2: = “

End Sub

1.3 Example 3: Filter data by multiple criteria but different columns

Suppose we have the following data file:

With Autofilter, we can filter multiple columns at once.

For example, if I want to filter all records in which the Printer and Sales Rep items are Mark, we can use the following code:

Sub FilterRows ()

With Worksheets (“Sheet1”). Range (“A1”)

.AutoFilter field: = 2, Criteria1: = “Printer”

.AutoFilter field: = 3, Criteria1: = “Mark”

End With

End Sub

1.4 Example 4: Using Autofilter to filter the Top 10 data

Suppose we have the following data table:

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

Sub FilterRowsTop10 ()

ActiveSheet.Range (“A1”). AutoFilter Field: = 4, Criteria1: = “10”, Operator: = xlTop10Items

End Sub

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

Note, in the example above if we want the top 5 items, we just need to change the number in Criteria1: = “10 ” from 10 to 5.

The code used to get top 5 items looks like:

Sub FilterRowsTop5 ()

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

End Sub

No matter how many top entries we want to get, the Operator value is always xlTop10Items.

Similarly, to get the top 10 lowest items, we use the following code:

Sub FilterRowsBottom10 ()

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

End Sub

1.5 Example 5: Filtering 10% of data by AutoFilter method

We continue to use the data table in the example above.

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

Sub FilterRowsTop10 ()

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

End Sub

Because in our data table there are 20 records, the above code will return the top 2 records (accounting for 10% of the total number of records).

1.6 Example 6: Using wildcards in Autofilter

For the data sheet as below:

To filter all rows whose names contain items from the Board, we use the following code:

Sub FilterRowsWildcard ()

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

End Sub

In the code above, we use wildcards * (asterisk) before and after from the Board (which is the criteria).

An asterisk can represent any number of characters. So the above code will filter out any entries from the Board in it.

1.7 Example 7: Copy filtered rows into new worksheet

If we want to filter records based on criteria and copy filtered rows, we can use the macro below. The macro will copy the filtered rows, add a new worksheet, and then paste the filtered rows into the new workbook:

Sub CopyFilteredRows ()

Dim As As Range

Dim ws As Worksheet

If Worksheets (“Sheet1”). AutoFilterMode = False Then

MsgBox “no locale is available”

Exit Sub

End If

Set rng = Worksheets (“Sheet1”). AutoFilter.Range

Set ws = Worksheets.Add

rng.Copy Range (“A1”)

End Sub

The above code checks to see if there are any rows filtered in Sheet1. If no rows are filtered, it displays a notification dialog.

If there are filtered rows, it will copy those rows, insert a new worksheet and paste the filtered rows into the newly inserted worksheet.

1.8 Example 8: Copy filtered columns into new worksheet

If we want to filter records based on criteria and copy filtered columns, we can use the macro below. The macro will copy the filtered columns, add a new worksheet, and then paste these columns into a new worksheet:

Sub CopyFilteredRows ()

Dim As As Range

Dim ws As Worksheet

If Worksheets (“Sheet1”). AutoFilterMode = False Then

MsgBox “no password can be found”

Exit Sub

End If

Set rng = Worksheets (“Sheet1”). AutoFilter.Range

Set ws = Worksheets.Add

rng.Copy Range (“A1”)

End Sub

The above code checks to see if there are any columns filtered in Sheet1. If no columns are filtered, it displays a message box.

If there are filtered columns, it will copy those columns, insert a new worksheet and paste the filtered columns into the newly inserted worksheet.

1.9 Example 9: Filter data based on cell value

By using Autofilter in VBA with the drop-down menu, we can create a function in which when selecting an item in the menu, all records of that item will be filtered.

This type of structure can be useful in situations if you want to filter data quickly, then use it for other tasks.

To do this, we use the following code:

Private Sub Worksheet_Change (ByVal Target As Range)

If Target.Address = “$ B $ 2” Then

If Range (“B2”) = “All” Then

Range (“A5”). AutoFilter

Else

Range (“A5”). AutoFilter Field: = 2, Criteria1: = Range (“B2”)

End If

End If

End Sub

It is the spreadsheet event code, executed only when there is a change in the worksheet and the destination cell is B2 (where we have the drop-down menu).

Also if the If Then Else condition is used to check if the user has selected All from the menu or not. If All is selected, all data will be displayed.

Note that this code is not placed in the module. Instead we put it in the backend of the spreadsheet with this data.

Follow the steps below to place the code in the spreadsheet code window:

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

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

Step 3: On the spreadsheet code window, copy and paste the code snippet above.

Step 4: Close the VB Editor window.

From now on using the drop down menu, it will automatically filter the data.

https://thuthuat.taimienphi.vn/su-dung-autofilter-in-vba-phan-2-45661n.aspx
The article Using Autofilter in VBA (Part 2) above Taimienphi.vn has just introduced you to some examples of using Autofilter in VBA. If you have any questions or questions, readers can leave your comments in the comment section below the article.