Md. Sourov Hossain Mithun
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.
Let’s get introduced to our dataset first that 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:
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
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.
- 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.
Read More: Excel Filter Data Based on Cell Value (6 Efficient Ways)
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
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.
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. 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.
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.
Here’s the output of the multiple criteria.
Read More: Excel VBA to Filter in Same Column by Multiple Criteria (6 Examples)
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.
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.
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
- How to Copy and Paste When Filter Is Applied in Excel
- How to Filter Unique Values in Excel (8 Easy Ways)
- Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)
- How to Use Text Filter in Excel (5 Examples)
- How to Filter by Date in Excel (4 Quick Methods)
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.
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.
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. 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.
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. 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
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. 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.
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)
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.
