VBA Code to Filter Data in Excel (8 Examples)

The Excel Sort & Filter command has a limited range to Filter data. But using VBA you can Filter data in a wide range as you wish. This article will provide you with 8 useful examples of using VBA code to Filter data in Excel using VBA codes with vivid illustrations.

Let’s get introduced to our dataset first which represents some students’ Gender, Status, and Age.

1. Using VBA Code to Filter Data Based on Text Criteria in Excel

In our very first example, we’ll use VBA to Filter only Male students from the Gender column of the dataset.

Steps:

• Right-click on the sheet title.
• Then select View Code from the Context menu.

Soon after, a VBA window will open up.

• Type the following codes in it-

Sub Filter_Data_Text()
Worksheets("Text Criteria").Range("B4").AutoFilter Field:=2, Criteria1:="Male"
End Sub

• Later, minimize the VBA.

• After that, to open the Macros dialog box, click as follows: Developer > Macros.

• Select the Macro name as mentioned in the codes.
• Finally, just press Run.

Now have a look that we have got only Male students’ data after Filtering.

2. Applying VBA Macro to Filter Data with Multiple Criteria in One Column

Here, we’ll Filter for multiple criteria in one column. From column number three of the dataset, we’ll Filter for Graduate and Postgraduate students.

Steps:

• Follow the first two steps of the first example to open the VBA window.
• Later, type the following codes in it-

Sub Filter_One_Column()
Worksheets("One Column").Range("B4").AutoFilter Field:=3, Criteria1:="Graduate", Operator:=xlOr, Criteria2:="Postgraduate"
End Sub

• Then minimize the VBA

• At this moment, follow the third step from the first example to open the Macros dialog box.
• Later, select the specified Macro name and press Run.

Soon after, you will get the Filtered rows based on multiple criteria like the image below.

Read More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)

3. Applying VBA to Filter Data with Multiple Criteria in Different Columns

Now we’ll Filter for multiple criteria- Male and Graduate students.

Steps:

• Follow the first two steps of the first example to open the VBA
• Later, write the following codes in it-

Sub Filter_Different_Columns()
With Worksheets("Different Columns").Range("B4")
.AutoFilter Field:=2, Criteria1:="Male"
.AutoFilter Field:=3, Criteria1:="Graduate"
End With
End Sub

• After that minimize the VBA window.

• Then follow the third step from the first example to open the Macros dialog box.
• Later, select the specified Macro name and press Run.

Here’s the output of the multiple criteria.

Read More: How to Filter Based on Cell Value Using Excel VBA

4. Using VBA Code to Filter Top 3 Items in Excel

In this example, we’ll Filter the top three students according to their age.

Steps:

• Follow the first two steps of the first example to open the VBA window.
• Then type the following codes in it-

Sub Filter_Top3_Items()
ActiveSheet.Range("B4").AutoFilter Field:=4, Criteria1:="3", Operator:=xlTop10Items
End Sub

• After that minimize the VBA window.

• Now follow the third step from the first example to open the Macros dialog box.
• Then select the Macro name as mentioned in the codes and press Run.

Then you will get the output like the image below-

Read More: Excel VBA: How to Filter with Multiple Criteria in Array

5. Applying VBA Code to Filter Top 50 Percents in Excel

Let’s use VBA codes to Filter the top fifty percent of students based on their age.

Steps:

• First, Follow the first two steps of the first example to open the VBA window.
• Later, type the following codes in it-

Sub Filter_Top50_Percent()
ActiveSheet.Range("B4").AutoFilter Field:=4, Criteria1:="50", Operator:=xlTop10Percent
End Sub

• Minimize the VBA window.

• At this moment, follow the third step from the first example to open the Macros dialog box.
• Then select the specified Macro name and press Run.

There was a total of 7 students so for 50 percent, it is showing approximately three students.

Read More: How to Remove Filter in Excel VBA

6. Applying VBA Code to Filter Data Using Wildcard

We can use Wildcard characters-* (asterisk) in VBA codes to Filter data in Excel. From the Status column, we’ll Filter only the values which contain ‘Post’.

Steps:

• Follow the first two steps of the first example to open the VBA window.
• Then write the following codes in it-

Sub Filter_with_Wildcard()
ActiveSheet.Range("B4").AutoFilter Field:=3, Criteria1:="*Post*"
End Sub

• Later, minimize the VBA window.

• Now follow the third step from the first example to open the Macros dialog box.
• Select the specified Macro name and press Run.

Then you will get the desired output.

7. Copying Filtered Data in a New Sheet with Excel VBA

See that there are some Filtered data in my dataset. Now I’ll copy them into a new sheet using VBA. These codes will not work properly in the sheet, you will have to apply them in the module.

Steps:

• Press Alt+F11 to open the VBA

• Then click Insert > Module to open a module.

• Now write the following codes-

Sub Copy_Filtered_Data_NewSheet()
Dim xRng As Range
Dim xWS As Worksheet
If Worksheets("Copy Filtered Data").AutoFilterMode = False Then
MsgBox "Noo filtered data"
Exit Sub
End If
Set xRng = Worksheets("Copy Filtered Data").AutoFilter.Range
Set xWS = Worksheets.Add
xRng.Copy Range("G4")
End Sub

• Then minimize the VBA

• Later, follow the third step from the first example to open the Macros dialog box.
• Then select the specified Macro name and press Run.

Now see that Excel has opened a new sheet and copied the Filtered rows.

Read More: Excel VBA to Filter in Same Column by Multiple Criteria

8. Applying VBA Code to Filter Data Using Drop-Down List

In our last example, we’ll make a drop-down list for genders first then we’ll use that to Filter data. For that, I have placed the gender criteria in another location and we’ll make a drop-down list in Cell D14.

Steps:

• Select Cell D14.
• Then click as follows: Data > Data Tools > Data Validation > Data Validation.

Soon after, a dialog box will open up.

Select List from the Allow drop-down.

Then click on the Open icon from the Source box.

Now select the criteria range and hit the Enter button.

• At this moment, just press OK.

Now our drop-down list is ready.

• Now Follow the first two steps of the first example to open the VBA window.
• Then write the following codes in it-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$14" Then
 If Range("D14") = "All" Then
  Range("B4").AutoFilter
 Else
  Range("B4").AutoFilter Field:=2, Criteria1:=Range("D14")
 End If
End If
End Sub

• Then minimize the VBA window.

• Now just select criteria from the drop-down list and the Filter will be activated.

Here’s the Filtered output after selecting Male from the drop-down.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Conclusion

I hope the procedures described above will be good enough to use VBA code to Filter data in Excel. Feel free to ask any question in the comment section and please give me feedback.

Related Articles

• Excel VBA: Filter Based on Cell Value on Another Sheet
• Excel VBA: Filter Table Based on Cell Value
• Filter Different Column by Multiple Criteria in Excel VBA