We usually use Filters in Microsoft Excel when we need to separate specific data from bulk data. The advanced filter adds some extra facilities. Excel VBA has this advanced filter option too. We will show some examples of Excel VBA with advanced filter criteria here.
Excel VBA Examples of Advanced Filter with Criteria: 6 Cases
6 examples of Excel VBA advanced filter criteria will be shown in this section.
We will use the following dataset for this purpose.
Example 1: Excel VBA to Filter Data in Current Location
We will apply VBA Macro to filter data based on criteria and place the filtered data at the present location.
In the range B16:E17, we set the criteria.
Step 1:
- First, go to the Developer tab.
- Click on the Record Macro from the ribbon.
- A new dialog box will appear. Set a name for the Macro there and press OK.
Step 2:
- Now, click on Macros on the ribbon.
- Choose the Macro and Step Into it.
Step 3:
- Now, copy the following VBA code and put this on the command module.
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:
- Now, press the F5 button to run the code.
We set the criteria to show the sales of the Chicago store only and here is the result.
Here, are our data and the criteria in the same sheet.
Example 2: Excel VBA for Advanced Filter When Data and Criteria Given in Different Sheets
In this example, we will show how to face a situation when our criteria and data are present on different sheets.
Our data is on the sheet named Criteria_Different_Sheet and criteria are given in sheet Criteria.
Step 1:
- Press Alt+F11 to enter the command module.
- Put the code below in the command module.
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:
- Hit F5 to run the code.
We can filter data when criteria are given on another sheet in this way.
Read More: VBA to Copy Data to Another Sheet with Advanced Filter in Excel
Example 3: Excel VBA to Filter Data and Paste in Different Location of the Same Sheet
If you want to paste our filtered data in a different location, follow the steps below.
Step 1:
- Hit the Alt+F11 to enter the command module.
- Write the following code on the command module.
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:
- Hit the F5 button and the code will run.
See the filtered data is pasted on another location. CopyToRange command specifies a different location to paste data.
Example 4: Filter Unique Data Only Using Excel VBA
We can get the unique data from this Excel VBA advance filter. In this process, only the 1st occurrences will be present in the dataset and the 2nd or further occurrences will remove.
Step 1:
- Enter the command module by pressing Alt+F11.
- Put the following code on the command module.
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:
- Now, run the code by pressing F5.
We marked the 1st occurrence by green color and the 2nd occurrence by yellow color. After running the code, the yellow-colored cell is removed.
Example 5: Filter and Remove Duplicate Without Criteria in Excel VBA
We can remove the duplicates without setting up any criteria. See the steps below.
Step 1:
- Go to the VBA command module pressing the Alt+F11
- Copy and paste the following code on the command module.
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 the F5 key and the code will run.
Look at the dataset. We marked the duplicated here. After applying the code, the 1st occurrence remains present on the dataset and the rest are removed.
Example 6: Insert Operator Sign-on Criteria to Filter Data
We can use operators in criteria to filter our data. See the following steps for that.
Step 1:
- Press Alt+F11 and enter the command module.
- Put the code below on the module.
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
Step 2:
- Hit F5 to run the code.
In the result, we get values that are more than $1.00.
Things to Remember
- Each of the data columns must have a unique heading.
- No blank rows are allowed in the dataset.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we’ve shown 6 examples of Excel VBA advanced filter criteria. I hope this will satisfy your needs. You are welcome to give your suggestions in the comment box.
