Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel VBA Examples with Advanced Filter Criteria (6 Cases)

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Examples of Excel VBA Advanced Filter with Criteria

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.

Excel VBA to Filter Data in Current Location

Step 2:

  • Now, click on Macros on the ribbon.
  • Choose the Macro and Step Into it.

Excel VBA to Filter Data in Current Location

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

Excel VBA to Filter Data in Current Location

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.

Read More: How to Apply the Advanced Filter to Copy to Another Location in Excel


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.

Excel VBA for Advanced Filter When Data and Criteria Given in 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: Apply Advanced Filter Based on Multiple Criteria in One Column 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

Excel VBA to Filter Data and Paste in Different Location of the Same Sheet

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.

Related Content: How to Use Advanced Filter to Copy Data to Another Sheet in Excel


Similar Readings:


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

Filter Unique Data Only Using Excel VBA

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.

Read More: How to Use Advanced Filter for Unique Records Only in Excel


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

Filter and Remove Duplicate Without Criteria in Excel VBA

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.

Read More: Excel Advanced Filter Not Working (2 Reasons & Solutions)


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

Insert Operator Sign-on Criteria to Filter Data with Excel VBA

Step 2:

  • Hit F5 to run the code.

In the result, we get values that are more than $1.00.

Read More: How to Use the Advanced Filter in VBA (A Step-by-Step Guideline)


Things to Remember

  • Each of the data columns must have a unique heading.
  • No blank rows are allowed in the dataset.

Conclusion

In this article, we’ve shown 6 examples of Excel VBA advanced filter criteria. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo