Excel VBA - Advanced Filtering Tutorial

What is VBA?

VBA is a programming language that can automate tasks within Excel using macros. You can calculate, move and manipulate data using this language. This is particularly useful for repetitive tasks that don’t have a single formula fix.

If you're looking for more background, check out this video on the differences between macros and VBA.

If you already have some experience creating macros, this article will show you how to take your skills to the next level with the Excel VBA advanced filter.

What is advanced filtering?

VBA advanced filtering is used for more complex filtering needs that the AutoFilter in Excel cannot complete.

You can filter out unique items, extract specific words or dates and even copy them to another document or sheet. In this article, we will be using VBA to control advanced filtering – but first, we need to show you how to build and setup your document so it is ready for VBA advanced filtering. 

How to set up advanced filtering

Here are the steps for setting up the data ready for advanced filtering. I will be using a blue table for the Data Range, and green for the Criteria Range, as shown in the screenshots below.

Database

• Set up a table or arrangement of data with header names
• Each header needs a unique name otherwise it will cause issues when filtering
• The rows below the headers should contain the data you wish to filter
• No blank rows can be in the data set (apart from the last row at the bottom)

Criteria Range

The criteria range are the rules that will be applied to the data when using the VBA Advanced Filter. You can set one or multiple criteria.

• This can be set up as a range of cells or as a table 
• To set up a table, simply select the range of cells including the headers and click Insert → Table 
• You can rename your table by clicking anywhere on the new table and editing the text where it says ‘Table1’
• Note: The headers need to match the headers from the database exactly     
• You don’t need to have every header – just the ones you want to filter by

• You can use operators within the cell such as:

  • < Less than
  • > Greater than
  • >= Greater than or equal to
  • <= Less than or equal to
  • <> Not equal to

For example, <1000 in the cell would return items that are less than 1000

Extract Range

This tool allows you to set an area of data to copy or move. This is particularly useful if, for example, you want to export out a list of contacts or locations. To set this up:

• The same rule applies here as the criteria range; the headers need to be exactly the same
• Note: The headers don’t need to be in the same order and you don’t have to filter all of the columns – just the ones you want to see. For example, if you wanted just a list of names then you would have only the ‘Name’ column on your chosen extract sheet.

Apply the advanced filter

This is to set the area you want to copy the filtered items to.

• Select any cell in the database
• On the Excel Ribbon Data tab, click advanced filtering

• You can choose to filter in place or to another location, depending on how you want to extract the data
• Set the criteria range
• Note: When copying to another location Excel will clear any data already stored in your extract location when the filter is applied
• Click OK

Filtering unique items

There is an option when filtering to only return unique items. This will return only 1 record that meets each of the criteria you have set. To do this:

• Simply tick the unique box when filtering

Setting up VBA

Advanced filtering is all well and good, but we have to click 'advanced filter' every time we want to filter the list down. This is fine, but it’s not why you came to this article!

First, we need to access the Visual Basic screen in Excel – by default, this is turned off. To turn it on go to:

•  File → Options → Ribbon Settings and turn on the Developer tab
• This should turn on an extra tab in Excel so you can create, record and modify VBA code using Macros.
• There are a few kinds of Macros – we will cover:
  • Macros that run by clicking on a button
  • Macros that run when a cell or range is modified

Button VBA macro

To create a button that triggers a VBA macro we need to create a ‘Module’ within VBA. To do this:

• Navigate to the Developer tab and click on ‘Visual Basic’
• Right-click on ‘Excel Objects Folder’ and click ‘Create New Module’:

Once you’ve created the new module we can start writing a Macro:

• We always start with Sub NameOfTheSubroutine () and end with End Sub
• We would write our code in-between those lines. For example:

Sub Advanced Filtering ()
End Sub

Here is the start of our VBA code that would go in between the lines above:

• The "Database" would be replaced with the range area of our data
• The "Criteria" would be replaced by our Criteria range

Range("Database").AdvancedFilter _ 
Action:=xlFilterInPlace, _ 
CriteriaRange:=Range("Criteria")

VBA Code so far:

Sub Advanced_Filtering() 

Range("C6:F23").AdvancedFilter _ 
Action:=xlFilterInPlace, _ 
CriteriaRange:=Range("C2:F3") 

End Sub

We now need to add a button:

• Insert → Shape → Pick a Shape and draw it onto the sheet. In this case, we’ll use a rectangle.

• Assign the Macro you have just created by right-clicking on the shape → Assign Macro. Test it by typing in the criteria range and click the button you’ve just added

Automatic VBA macro

We’ve just created a button to trigger our macro, but that’s an extra step – we want the list to just filter when we enter something into those cells. To do this we can use Worksheet_Change to trigger the Macro we have just created to run when we update the criteria changes.

• Right-click on the sheet and click ‘View Code’ – if this doesn’t work you can access via Visual Basic view on the Developer tab. Then double-click on the sheet with your criteria range on it (In this example it’s sheet 1).

Another way to trigger a VBA Macro is to get Excel to automatically trigger whenever a sheet is updated - In other words when you add, update or change a cell.

This is great, but we don't want Excel to trigger the code when any cell is updated - We only want to trigger the Macro when a specific range is updated.

• The code below will first check to see if the cells updated are C3 to F3 (our criteria range) and if it’s not it will kill the subroutine (Then Exit Sub).
• If it is within those cells, it triggers our first Macro to run
• This is useful and important as will only allow the code to run when we edit those cells
• If we didn't have the ‘If Intersect’, it would run every time any cell is changed

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C3:F3")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub 
Call Advanced_Filtering 

End Sub

VBA copy to a new location

To alter the VBA to copy to a new location we simply need to change 2 parts of the code:

• We need to change Action:=xlFilterInPlace to Action:=xlFilterCopy
• And add CopyToRange:=Sheets("SHEET NAME").Range("RANGE") to the end
• Here’s what the final code should look like. This will filter the data from Sheet 1 into Sheet 1 in the range A1:D1:

Sub Advanced_Filtering() 

CriteriaLastRow = 4 'Last Row you have in the Criteria range 

For i = 3 To CriteriaLastRow 'Loops through until the last Row 
  RowsCount = Application.WorksheetFunction.CountA(Range("C" & i & ":F" & i)) 
  If RowsCount = 0 Then CriteriaRowsSet = i - 1 Else CriteriaRowsSet = CriteriaLastRow 'Checks to see if any row returns 0 and sets it to the row above's number 
Next i 

Range("C6:F23").AdvancedFilter _ 
Action:=xlFilterInPlace, _ 
CriteriaRange:=Range("C2:F" & CriteriaRowsSet), _ CopyToRange:=Sheets("Sheet2").Range("A1:D1") 

End Sub

What we have gone through so far just covers the basics of filtering, but there is much more filtering is capable of. Let's take a look at VBA advanced filtering with multiple criteria and wildcards.

Advanced filtering with multiple criteria

Currently, the criteria range can only handle AND statements – meaning it would need to meet all of the criteria to display after filtering. If I wanted to do an OR statement (i.e. I want to show results that are either Wang OR John) then I would need to expand my criteria table:

• First I would expand my row at the bottom to allow for more statements
• Expand both Macros to allow for the extra row
• Note: Make sure when moving or adding new rows that the Macros are still looking at the correct range. Macros are not like formulas that automatically change when you add new rows
• If you do need to add/remove cells then you would need to go back into the VBA code and extend or shorten the range of cells within the code.

If we wanted to remove a column, for example, we would change this code:

Range("C6:F23").AdvancedFilter _ 
Action:=xlFilterInPlace, _ 
CriteriaRange:=Range("C2:F" & CriteriaRowsSet), _ 
CopyToRange:=Sheets("Sheet2").Range("A1:D1")

To this code:

Range("C6:E23").AdvancedFilter _ 
Action:=xlFilterInPlace, _ 
CriteriaRange:=Range("C2:E" & CriteriaRowsSet), _ 
CopyToRange:=Sheets("Sheet2").Range("A1:D1")

Notice I changed both the Filter area and the range Criteria. This is showing me records that are for Wang AND 2/2/2018 OR John:

Note: This will not work unless something is contained within both rows or all of the rows if you have more. So we will need to add some code to find which is the bottom row:

• The code below starts off and sets the last row to 4 (meaning the last row number for your criteria)
• It then loops through and checks each row from 3 to your last row and counts how many items are in there
• If it gets right to the bottom and nothing is 0 it sets the criteria range to your full criteria data
• If it returns a 0 on any line it sets the range to the row above it

Sub Advanced_Filtering()

CriteriaLastRow = 4 'Last Row you have in the Criteria range 

For i = 3 To CriteriaLastRow 'Loops through until the last Row 
  RowsCount = Application.WorksheetFunction.CountA(Range("C" & i & ":F" & i))
  If RowsCount = 0 Then CriteriaRowsSet = i - 1 Else CriteriaRowsSet = CriteriaLastRow 'Checks to see if any row returns 0 and sets it to the row above's number. 
Next i

  Range("C6:F23").AdvancedFilter _
  Action:=xlFilterInPlace, _
  CriteriaRange:=Range("C2:F" & CriteriaRowsSet)

End Sub

Advanced filtering with wildcards

Wildcard filtering is where we use a special character to replace a letter to give us similar results.

To use a Wildcard we wouldn’t need to amend any data or VBA code. We would use a symbol within the search criteria.

• The asterisk (*) wildcard character represents any number of characters in that position, including zero characters.
• The question mark (?) wildcard character represents one character in that position.

For example, *ang would return anything that ended with the letters ang:

In summary

So, what have we learned?

• That almost any task/function in Excel can be automated using VBA
• How to filter down data more precisely and accurately using VBA
  • How to filter down by wildcards
  • How to move our filtered data to another location
  • How to filter using multiple criteria
• The basics of being able to loop through data and perform a function on each line. This is not just limited to advanced filtering as it can be used with other Excel functions
• The different triggers for VBA in Excel:
  • Button – A Macro will run on a click of a button
  • Call – A Macro will be ‘called’ (triggered) from another Macro
  • Change – A Macro will run when a sheet is updated

This tutorial only covers a small portion of the capability VBA and Macros have in Excel. If you’d like to learn more about creating powerful macros to automate your tasks, check out our Macros and VBA course where you'll learn how to use functions like VBA range object and more.

Now that you've got filtering down pat, why don't you try creating your own user-defined functions? Learn from the pros with our step-by-step guide on how to build Excel UDFs.

Ready to become a certified Excel ninja?

Take GoSkills' Macros & VBA course today!

Start free trial