Implement Advanced Filter in Excel with VBA

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 Sub

• Final step is to assign this new macro to the button we inserted in the Output sheet. Close the VBA editor and then
  1. Right click on the Rectangle Shape
  2. Click on Assign Macro
  3. 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?

1. In the OUTPUT sheet, keep only those 3 columns which you require
2. Create a new blank sheet named “Temp”
3. 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

1. Data Validation
2. 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

That’s it for now, hope this was helpful. Stay tuned for more!