VBA Code to Filter Data in Excel (8 Examples)

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 to use VBA code to Filter data in excel using VBA codes with vivid illustrations.


Download Practice Workbook

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


8 Examples to Use VBA Code to Filter Data in Excel

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


1. Use VBA Code to Filter Data Based on a 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.

Use VBA Code to Filter Data Based on a Text Criteria in Excel

  • 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

Use VBA Code to Filter Data Based on a Text Criteria in Excel

Code Breakdown

  • Here, I created a Sub procedure, Filter_Data_Text().
  • Then used the Range property to declare our respective sheet name and range
  • Next, I used the AutoFilter method to use Criteria of my choice where Field:=2 means column 2. And Criteria1:=”Male” to Filter the data for Male.

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

Use VBA Code to Filter Data Based on a Text Criteria in Excel

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

Use VBA Code to Filter Data Based on a Text Criteria in Excel

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

Read More: Excel Filter Data Based on Cell Value (6 Efficient Ways)


2. Apply VBA Code 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:

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

Code Breakdown

  • Here, I created a Sub procedure, Filter_One_Column().
  • Then used the Range property to declare our respective sheet name and range
  • Next, I used the AutoFilter method to use the Criteria of my choice where Field:=3 means column 3. Here, Criteria1:=”Graduate” and Criteria2:=”Postgraduate” to Filter the student’s Status.
  • Finally, I used Operator:=xlOr to apply OR condition Filter for multiple criteria.

  • 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.

Apply VBA Code to Filter Data with Multiple Criteria in One Column

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

Read More: Filter Multiple Criteria in Excel (4 Suitable Ways)


3. Apply VBA Code to Filter Data with Multiple Criteria in Different Columns in Excel

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

Steps:

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.

Apply VBA Code to Filter Data with Multiple Criteria in Different Columns in Excel

Code Breakdown

  • Here, I created a Sub procedure, Filter_Different_Columns().
  • Then, I used the With statement to use Multiple Column.
  • Then used the Range property to declare our respective sheet name and range
  • Next, I used the AutoFilter method to use Criteria of my choice where Field:=2 means column 2 and Field:=3 means column 3.
  • Here, selected Criteria1:=”Male” for Gender column and Criteria1:=”Graduate” for Status column to Filter data from different columns.

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

Apply VBA Code to Filter Data with Multiple Criteria in Different Columns in Excel

Here’s the output of the multiple criteria.

Apply VBA Code to Filter Data with Multiple Criteria in Different Columns in Excel

Read More: Excel VBA to Filter in Same Column by Multiple Criteria (6 Examples)


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

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

Steps:

Sub Filter_Top3_Items()
ActiveSheet.Range("B4").AutoFilter Field:=4, Criteria1:="3", Operator:=xlTop10Items
End Sub
  • After that minimize the VBA window.

Use VBA Code to Filter Top 3 Items in Excel

Code Breakdown

  • Here, I created a Sub procedure, Filter_Top3_Items().
  • And then used Operator:=xlTop10Items to Filter for the top three data.

  • 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.

Use VBA Code to Filter Top 3 Items in Excel

Then you will get the output like the image below-

Read More: How to Filter Based on Cell Value Using Excel VBA (4 Methods)


Similar Readings


5. Use 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:

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

Use VBA Code to Filter Top 50 Percents in Excel

Code Breakdown

  • Here, I created a Sub procedure, Filter_Top50_Percent().
  • Later, used Operator:=xlTop10Percent to Filter the top fifty percent from column-4.

  • 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.

Use VBA Code to Filter Top 50 Percents in Excel

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

Read More: How to Use Profit Percentage Formula in Excel (3 Examples)


6. Apply 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:

Sub Filter_with_Wildcard()
ActiveSheet.Range("B4").AutoFilter Field:=3, Criteria1:="*Post*"
End Sub
  • Later, minimize the VBA window.

Apply VBA Code to Filter Data Using Wildcard

Code Breakdown

  • Here, I created a Sub procedure, Filter_with_Wildcard().
  • Then used Range(“B4”) to set the range.
  • Next, used AutoFilter to Filter in Field:=3 means column 3.
  • Criteria1:=”*Post*” to Filter the values which contain ‘Post’.

  • 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.

Read More: How to Add Filter in Excel (4 Methods)


7. Embed Excel VBA to Copy Filtered Data in a New Sheet in Excel

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

Embed Excel VBA to Copy Filtered Data in a New Sheet in Excel

  • Then click Insert > Module to open a module.

Embed Excel VBA to Copy Filtered Data in a New Sheet in Excel

  • 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

Code Breakdown

  • Here, I created a Sub procedure, Copy_Filtered_Data_NewSheet().
  • After that declared two-variable- xRng As Range and xWS As Worksheet.
  • Then used an IF statement to check Filtered
  • Later, used MsgBox to show the output.
  • Then used Worksheets(“Copy Filtered Data”).AutoFilter.Range to select the Filtered range and used Add to add a new sheet.
  • Finally, Copy Range(“G4”) will copy the Filtered data to the new sheet.

  • 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: Shortcut for Excel Filter (3 Quick Uses with Examples)


8. Apply 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.

Apply VBA Code to Filter Data Using Drop Down List

Select List from the Allow drop-down.

Then click on the Open icon from the Source box.

Apply VBA Code to Filter Data Using Drop Down List

Now select the criteria range and hit the Enter button.

Apply VBA Code to Filter Data Using Drop Down List

  • At this moment, just press OK.

Now our drop-down list is ready.

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.

Code Breakdown

  • Here, I created a Private Sub procedure, Worksheet_Change(ByVal Target As Range).
  • Then, I selected Worksheet from General and Change from Declarations.
  • Then set the Address to know the location.
  • Finally within the IF statement used the AutoFilter method with Field and Criteria

  • 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.

Read More: How to Filter by List in Another Sheet in Excel (2 Methods)


Practice Section

You will get a practice sheet in the Excel file given above to practice the explained ways.


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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo