Excel Advanced Filter Multiple Criteria
- Excel Advanced Filter Multiple Criteria
- Excel Advanced Filter with Multiple Criteria
- Template for Excel Advanced Filter
- Excel VBA Filter with Multiple Criteria Video 1
- Adding a dynamic named ranges
- Add data validation
- Adding the Worksheet formulas
- Operators available to the advanced filter
- Recording our VBA Advanced Filter
- Run an Advanced Filter
- Clear our data
- Assigning the macros
Excel Advanced Filter with Multiple Criteria
I have had a lot of enquiries from the previous multiple criteria advanced filter tutorial. In this tutorial I will deal with having multiple criteria and allowing for any field to be blank. I will also show you how it is possible to allow for partial lookups.
Template for Excel Advanced Filter
I would recommend that you download the template because it will make it easier for you to complete this tutorial. The references below are designed for this template. So save time and heartache – use the template.
Excel VBA Filter with Multiple Criteria Video 1
This video is designed to help you understand how the application will work. It will also give you step-by-step instructions on how to
- create the dynamic named ranges/
- adding the data validation/
- setting up the formulas/
- and recording the macro is that are necessary to run the filter/
You will learn a lot from this video so take the time to watch at first.
Adding a dynamic named ranges
On the ribbon select Formula / Name manager /New
Type the name into the Name box and the range formula goes into the Refers to:
Test the named range. If it is a dynamic named range it will need to have some values in the cells for it to work.
Note: I will add a link to a dynamic named range tutorial below.
Here are the named ranges (names) and formulas.
Note: These formulas are added into the Name Manager not into worksheet cell.
Add data validation
Choose Data on the ribbon /Data Validation / List / click in the source and push the F3 key and select the named range. Your dynamic named range will now populate the list source for the data validation box.
- Add data validation Category to cell reference:
- Add data validation TaxPayers to cell reference:
- Add data validation Location to cell reference:
Adding the Worksheet formulas
Here are the formulas. Note that we are using a conditional or decision making IF statement. If the cell is found to be blank then we are adding operators that our advanced filter will recognize.
This is an important step so please make sure that you get this right.
Operators available to the advanced filter
To be able to enhance your filtering you can use operators. These are the most common operators and when you are proficient with these there are even others you can add to make your filters more effective.
< Less than
<= Less than or equal to
>= Greater than or equal to
<> Not equal to
Recording our VBA Advanced Filter
Select the Developer tab. If you cannot see the Developer tab click File/ Options / Customize the ribbon /in the right hand tab tick the box next to Developer.
Now click Record Macro and the macro dialog box will appear.
Give the macro a name (one word or multiple words joined with underscores) make sure the Store macro in: is This Workbook. Type FilterMe
Remember that Excel VBA is now recording every move you make.
So, it is good to practice running this advanced filter before recording the macro so that when you do record it will be really slick.
Run an Advanced Filter
On the Ribbon and choose Data and then Advanced.
Here are the parameters for the advanced filters
You must initiate this from the FilterData sheet.
Note: Make sure that you include the headers in your advanced filter in the three ranges. You must use exactly the same words in all three. For this reason it best to copy and paste them.
After you run the advanced filter do not forget to stop recording.
The macro that you see below is what should be in a module.
To go to the VBA editor hold down the Alt+ F11 key.
' Filterme Macro
' Filter the database
' Keyboard Shortcut: Ctrl+Shift+D
Sheets("Data").Range("D4:L39").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("M5:P6"), CopyToRange:=Range("C8:K8"), Unique:=False
Modify your code
We are going to make a couple of simple changes to this code. The two things that we want to change are:
Change the sheet reference to a code reference. We want our macro to work even if someone changes the sheet name. To allow this to happen we can change the reference to the sheet to the codename for the sheet.
Change:- Sheets(“Data”) to Sheet3
Change:- Range("D4:L133") to Range("D4").CurrentRegion
Here is the changed macro
'run the advanced filter
Sheet3.Range("D4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheet2.Range("M5:S6"), CopyToRange:=Sheet2.Range("C8:K8"), Unique:=False
Clear our data
Record a macro that clears the data under the header on the Interface sheet.
As mentioned in the video I have adjusted the recorded macro to make it a little cleaner and you will notice it will operate now without any flicker.
'declare the variables
Dim ws As Worksheet
' sheet variable
Set ws = Sheet2
'clear contents and format
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
.Borders.LineStyle = xlNone
Assigning the macros
Now comes the fun part. All we now need to do is assign our macros to the shapes on the FilterData sheet and we can see the power of the Excel in Action.
The next tutorial
In the next tutorial will expand on this and show you how to do all of this in a user form that we can run from another sheet. So all we need to do is show the user the user form and all of the data will be hidden from them.