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.

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.

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

• 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.

Sub CreateAdvancedFilter()
   Dim rngDatabase As Range
   Dim rngCriteria As Range
'define the database and criteria ranges
   Set rngDatabase = Sheets("Database").Range("A1:H50")
   Set rngCriteria = Sheets("Criteria").Range("A1:H3")
'filter the database using the criteria
   rngDatabase.AdvancedFilter xlFilterInPlace, rngCriteria
End Sub

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.

Sub ClearFilter()
   On Error Resume Next
'reset the filter to show all the data
   ActiveSheet.ShowAllData
End Sub

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

Sub UniqueValuesFilter1()
   Dim rngDatabase As Range
   Dim rngCriteria As Range
'define the database and criteria ranges
   Set rngDatabase = Sheets("Database").Range("A1:H50")
   Set rngCriteria = Sheets("Criteria").Range("A1:H3")
'filter the database using the criteria
   rngDatabase.AdvancedFilter xlFilterInPlace, rngCriteria,,True
End Sub

OR you need to use named arguments as shown below.

Sub UniqueValuesFilter2()
   Dim rngDatabase As Range
   Dim rngCriteria As Range
'define the database and criteria ranges
   Set rngDatabase = Sheets("Database").Range("A1:H50")
   Set rngCriteria = Sheets("Criteria").Range("A1:H3")
'filter the database using the criteria
   rngDatabase.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rngCriteria, Unique:=True
End Sub

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

Using the CopyTo argument

Sub CopyToFilter() 
   Dim rngDatabase As Range 
   Dim rngCriteria As Range 
'define the database and criteria ranges 
   Set rngDatabase = Sheets("Database").Range("A1:H50") 
   Set rngCriteria = Sheets("Criteria").Range("A1:H3") 
'copy the filtered data to an alternative location
    rngDatabase.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, CopyToRange:=Range("N1:U1"), Unique:=True
End Sub

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.

rngDatabase.AdvancedFilter xlFilterCopy, rngCriteria, Range("N1:U1"), True

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

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.

Sub RemoveDuplicates()
   Dim rngDatabase As Range
'define the database
   Set rngDatabase = Sheets("Database").Range("A1:H50")
'filter the database to a new range with unique set to true
rngDatabase.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("N1:U1"), Unique:=True
End Sub