How to use Excel VBA with an Advanced Filter

This is the sample dataset.

Example 1 – Use Excel VBA to Filter Data in the Current Location

Set the criteria in B16:E17.

Step 1:

Go to the Developer tab.
Click Record Macro.
Name the Macro and click OK.

Step 2:

Click Macros.
Select the Macro and click Step Into.

Step 3:

Enter the following VBA code:

Sub Filter_Criteria()
Dim data As Range
Dim criteria As Range
Set data = Range("B4:E14")
Set criteria = Range("B16:E17")
data.AdvancedFilter xlFilterInPlace, criteria
End Sub

Step 4:

Press F5 to run the code.

Example 2 – Using Excel VBA with an Advanced Filter When Data and Criteria are in Different Sheets

Step 1:

Press Alt+F11.
Enter the following VBA code:

Sub Filter_Criteria_2()
 Dim data As Range
 Dim criteria As Range
   Set data = Sheets("Criteria_Different_Sheet").Range("B4:E14")
   Set criteria = Sheets("Criteria").Range("B4:E5")
   data.AdvancedFilter xlFilterInPlace, criteria
End Sub

Step 2:

Press F5 to run the code.

Example 3 – Using Excel VBA to Filter Data and Paste it in a Different Location in the Same Sheet

Step 1:

Press Alt+F11.
Enter the following VBA code:

Sub Filter_Criteria_3()
 Dim data As Range
 Dim criteria As Range
Set data = Range("B4:E14")
Set criteria = Range("B16:E17")
data.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=criteria, CopyToRange:=Range("G4:J14")
End Sub

Step 2:

Press F5 to run the code.

Example 4 – Filter Unique Data Using Excel VBA

To filter the 1st occurrences:

Step 1:

Press Alt+F11.
Enter the following VBA code:

Sub Filter_Criteria_4()
 Dim data As Range
 Dim criteria As Range
Set data = Range("B4:E14")
Set criteria = Range("B16:E17")
data.AdvancedFilter xlFilterInPlace, criteria, , True
End Sub

Step 2:

Press F5 to run the code.

Example 5 – Filter and Remove Duplicates Without Criteria in Excel VBA

Step 1:

Press Alt+F11
Enter the following VBA code:

Sub Filter_Criteria_5()
 Dim data As Range
 Dim criteria As Range
Set data = Range("B4:E14")
data.AdvancedFilter xlFilterInPlace, , , True
End Sub

Step 2:

Press F5 to run the code.

Example 6 – Using an Operator to Filter Data

Step 1:

Press Alt+F11.
Enter the following VBA code:

Sub Filter_Criteria_7()
 Dim data As Range
 Dim criteria As Range
Set data = Range("B4:E14")
Set criteria = Range("B16:E17")
data.AdvancedFilter xlFilterCopy, criteria, Range("G4:J14")
End Sub