Return to VBA Code Examples

VBA Advanced Filter

This tutorial will explain the how to use the Advanced Filter method in VBA

Advanced Filtering in Excel is very useful when dealing with large quantities of data where you want to apply a variety of filters at the same time.  It can also be used to remove duplicates from your data.  You need to be familiar with creating an Advanced Filter in Excel before attempting to create an Advanced Filter from within VBA.

Consider the following worksheet.

vba advanced filtering database

You can see at a glance that there are duplicates that you might wish to remove.  The Type of account is a mixture of Saving, Term Loan and Check.

First you need to set up a criteria section for the advanced filter.  You can do this in a separate sheet.

vba advanced filtering criteriasheet

For ease of reference, I have named my data sheet ‘Database’ and my criteria sheet ‘Criteria’.

Advanced Filter Syntax

Expression.AdvancedFilter Action, CriteriaRange, CopyToRange, Unique

vba advanced filtering syntax

  • The Expression represents the range object – and can be set as a Range (eg Range(“A1:A50”) – or the Range can be assigned to a variable and that variable can be used.
  • The Action argument is required and will either be xlFilterInPlace or xlFilterCopy
  • The Criteria Range argument  is where you are getting the Criteria to filter from (our Criteria sheet above).  This is optional as you would not need a criteria if you were filtering for unique values for example.
  • The CopyToRange argument  is where you are going to put your filter results – you can filter in place or you can have your filter result copied to an alternative location.  This is also an optional argument.
  • The Unique argument is also optional – True is to filter on unique records only, False is to filter on all the records that meet the criteria – if you omit this, the default will be False.

Filtering Data In Place

Using the criteria shown above in the criteria sheet – we want to find all the accounts with a type of ‘Savings’ and ‘Current’.   We are filtering in place.

vba advanced filtering in place

The code will hide the rows that do not meet the criteria.

In the above VBA procedure, we did not include the CopyToRange or Unique arguments.

Resetting the data

Before we run another filter, we have to clear the current one.  This will only work if you have filtered your data in place.

Filtering Unique Values

In the procedure below, I have included the Unique argument but omitted the CopyToRange argument.  If you leave this argument out, you EITHER have to put a comma as a place holder for the argument

OR you need to use named arguments as shown below.

Both of the code examples above will run the same filter, as shown below – the data with only unique values.

vba advanced filtering unique

Using the CopyTo argument

Note that we could have omitted the names of the arguments in the Advanced Filter line of code, but using named arguments does make the code easier to read and understand.

This line below is identical to the line in the procedure shown above.

Once the code is run, the original data is still shown with the filtered data shown in the destination location specified in the procedure.

vba advanced filtering copy to location

Removing Duplicates from the data

We can remove duplicates from the data by omitting the Criteria argument, and copying the data to a new location.

 

vba filtering remove duplicates

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!