Advanced filters as the name suggests is one of the advanced functionalities within Excel which makes it really easy to filter out large datasets without any major lags. This enables the user to set a criteria and filter the data accordingly.
But did you know that Advanced Filters can be integrated with VBA to generate mind blowing results while preparing an intuitive dashboard or reports. We will see how a few lines of code, 9 lines to be exact can create a standalone tool to filter out large datasets. And the best part about it is that once you write this code, you would not have to change the code even if the dataset gets changed. (Read till the end..... there is a bonus scenario.....)
Data Used
The CSV file contains the New Zealand 2013 Census meshblock Business data.
What Will We Do?
We are going to see how to integrate Advanced Filter with VBA to generate filtering capabilities for the user which is faster & more user friendly than the traditional method. We will do this with just 9 lines of code & make it a universal application which will work with any dataset, which will not require any code changes going forward.
Setting Up the Workbook
Create 2 worksheets,
Raw Data - Paste your raw data downloaded from the link shared into this sheet
Output - This is the sheet where we will have the filtered output which will be generated using Advanced Filter & VBA
Paste the Header Values from the Raw Data into the sheet starting from cell B1
Insert a Rectangle Shape (or any shape) anywhere on the sheet. Preferably in Column A because we left it blank for this purpose only
Let's Code.. (Implement Advanced Filter with VBA)
Go into the VBA editor by pressing ALT + F11
In the VBA editor, Go to Insert & click on Module
Double Click on the newly created module, which will open up the code window for that module
Enter below code into the code window. I have commented each block of code in simple language, so that it's easier for you to understand
Sub AdvancedFilterCode()
'Declaring variables for using as range & worksheets
Dim inputrng As Range, outputrng As Range, criteriarng As Range
Dim wks1 As Worksheet, wks2 As Worksheet
'Defining the worksheet against the variable
Set wks1 = ThisWorkbook.Sheets("RawData")
Set wks2 = ThisWorkbook.Sheets("Output")
'Defining all required ranges for usage in the advanced filter code
Set inputrng = wks1.Range("A1").CurrentRegion
Set criteriarng = wks2.Range("B1").CurrentRegion
Set outputrng = wks2.Range("B15")
'Clearing any old data in output sheet
outputrng.CurrentRegion.Clear
'Running the advanced filter code for copying the data
inputrng.AdvancedFilter xlFilterCopy, criteriarng, outputrng
End SubFinal step is to assign this new macro to the button we inserted in the Output sheet. Close the VBA editor and then
Right click on the Rectangle Shape
Click on Assign Macro
Assign the newly created Macro i.e. AdvancedFilterCode. Press OK
That's it, we have successfully implemented Advanced Filter into the sheet using VBA. Now whenever a user clicks on the Filter button, he/she will be able to see the results instantly in the rows below.
In case you want to add more than 1 filter condition for a column then you can do that by entering the value below the first one like this
We have ample space to enter as many filter criteria in the row, since we started inserting the actual filtered data from Row 15. So if you want you can enter additional filter conditions within those rows.
Alternative Scenario : Only Selected Few Columns Needed
Now in case you require only few columns from the your larger dataset then you should make few changes in the workbook that you have already created above.
New Requirement
While applying Advanced Filter, need only 3 columns from Raw Data i.e.
Year, Industry_name_NZSIOC & Variable_code
What Changes Needs to be Done?
In the OUTPUT sheet, keep only those 3 columns which you require
Create a new blank sheet named "Temp"
Replace the previous code with below one:
Sub AdvancedFilterCode()
Application.ScreenUpdating = False
'Declaring variables for using as range & worksheets
Dim inputrng As Range, outputrng As Range, criteriarng As Range
Dim col1, col2, col3 As Range
Dim wks1 As Worksheet, wks2, wks3 As Worksheet
'Defining the worksheet against the variable
Set wks1 = ThisWorkbook.Sheets("RawData")
Set wks2 = ThisWorkbook.Sheets("Output")
'Defining all required ranges for usage in the advanced filter code.
Set inputrng = wks1.Range("A1").CurrentRegion
Set inputrng = inputrng.Resize(inputrng.Rows.Count, 3)
'This is where we are selecting the columns which needs to be selected. So in case you require some other columns then replace the numbers in bracket with some other column number
Set col1 = inputrng.Columns(1)
Set col2 = inputrng.Columns(4)
Set col3 = inputrng.Columns(6)
Set inputrng = Union(col1, col2, col3)
Set wks3 = ThisWorkbook.Sheets("Temp")
inputrng.Copy
wks3.Range("A1").PasteSpecial Paste:=xlPasteAll
Set inputrng = wks3.Range("A1").CurrentRegion
Set criteriarng = wks2.Range("B1").CurrentRegion
Set outputrng = wks2.Range("B5")
'Clearing any old data in output sheet
outputrng.CurrentRegion.Clear
'Running the advanced filter code for copying the data
inputrng.AdvancedFilter xlFilterCopy, criteriarng, outputrng
End Sub
Optional Step : Better User Experience
In Case you want to provide user of this file with better functionality then you always add a drop down list in the criteria section, so that they can simply select the desired filter instead of typing it in the cell.
There are 2 main ways to do this
Data Validation
Vba Dropdown
I personally prefer using Data Validation because that does not require any additional coding in my workbook, so the final product will be much more adaptable to future changes to the criteria values.
So to create a DROP DOWN using DATA VALIDATION, follow below steps:
Create a list of values that you would want to show in the drop down. I prefer creating a control sheet and placing all my values there
Now select each range of values seperately and give it a custom name, so that its easier for you to refer it later on. I have name it val_Year, val_Industries & val_Code
Select the cell where you want to apply the drop down and then go to Data >> Data Validation >> Data Validation
In the next prompt, select "List" from the drop down and write below formula for creating the drop down for Year
= val_Year
Do the above 2 steps for Industry & Code column as well
Finally you will have something like below, that is drop down option for each criteria cell which the user can select to filter the data
Just to polish the user experience further, I'm going to create a CLEAR FILTER button, so that if the user want, all the filters applied gets removed. You can add below code to clear the data and assign it to a button
Sub clearFilter()
Application.ScreenUpdating = False
Dim wks As Worksheet
Set wks = ThisWorkbook.Sheets("Output")
wks.Range("B2:D2").Value = ""
Call AdvancedFilterCode
End Sub
Comments