Dynamic Advanced Filter Excel (VBA & Macro)

Circumstances may demand different types of filter operations; advanced, dynamic, and so on. Our agenda for today is to show you how to use the dynamic advanced filter in Excel. For conducting the session we are using Excel 2019, you can choose your version.

First things first, let’s get to know about the workbook which is the base of our examples.

Excel Sheet - Dynamic Advanced Filter Excel

We have a basic table with the salesperson from the different regions along with their selling products and brands. Using this table we are going to show you how to use the dynamic advanced filter.

Note that this is a basic table with a simple scenario. practically you may encounter a much larger and complex dataset and scenario.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Basic of Advanced Filter

To comfort your surfing, Excel provides different advanced tools and features. One of these is Advanced Filter

Advanced Filter will be found in the Sort & Filter section of the Data tab.

Advanced Filter - Dynamic Advanced Filter Excel

To explore the feature, click on Advanced here. A dialog box will appear in front of you.

Advanced Filter box - Dynamic Advanced Filter Excel

You can see there are various options, you can filter in place or can copy the filtered data to another location.

There is an option Unique records only, that will produce only the distinctive data.

Generally, the Advanced Filter is used for copying the filtered data to another location. You need to select the Copy to another location option.

Criteria and destination range - Dynamic Advanced Filter Excel

To show you examples we have introduced two tables with identical columns.

You can see after selecting the option the Copy to field has been enabled. You need to insert the destination location here.

In the Criteria range, we will provide the filter criteria.

Set range, criteria - location - Dynamic Advanced Filter Excel

Here we have selected the entire table as our List range. And in the Criteria range provided $G$3:$J$4. Do you understand why?

You can see within our Criteria range we have inserted the Region value so far. If we don’t tell the US is value for Region, how will Excel know with which column it needs to match this? We need to specify the column, so we entered $G$3:$J$4.

Then in the Copy to, we have inserted $G$8:$J$8, only the column name, to make it understand which value in which column.

Advanced Filter result - Dynamic Advanced Filter Excel

We have found the value from the US region.

Now let’s insert another criteria value.

Basic Advanced Filter - Dynamic Advanced Filter Excel

We have chosen Mobile as Product Type, still, the filtered data has Laptop and Keyboard. To make the new criteria count we need to repeat the entire process.

Dynamic Advanced Filter

We have understood from the previous section that we need to execute the entire Advanced Filter process from the beginning if we change our filter criteria. It’s not an ideal approach for most real-life cases.

To make the Advanced Filter dynamic you need to use Macros. Depending upon your convenience, you can select different options to execute the Macros.

1. Macros with Keyboard Shortcut

You can use a keyboard shortcut to trigger your macros. As per our agenda, our Macros will be on the basis of the Advanced Filter. 

Table of list- location - Dynamic Advanced Filter Excel

We have rolled-backed a bit. We have the data and criteria but no filtered value yet. Now let’s start.

In the Code section from the Developer tab, you will find an icon for Record Macro. 

Record macros - Dynamic Advanced Filter Excel

Click that.

A new dialog box will pop up.

Macro dialog box - Dynamic Advanced Filter Excel

In this dialog box, you need to provide the Macro a name, your desired shortcut key, and the scope of the Macro.

Macro name - Dynamic Advanced Filter Excel

We have provided the macro name as Advanced_Filter. And have chosen CTRL + SHIFT + J as the shortcut key.

Please keep in mind the CTRL key is mandatory & Excel will automatically start the shortcut with this key.

Advanced Filter - Dynamic Advanced Filter Excel

Now we need to repeat the process of what we have done in the Basic of Advanced Filter section.

Choose the Advanced option from the Sort & Filter. 

Select range - Dynamic Advanced Filter Excel

Select the List range, Criteria range, and Copy to. And then click OK. 

Advanced Filter - Dynamic Advanced Filter Excel

So far we did the same as previous and our result is also the same.

Now from the Developer tab click Stop Recording. This will stop recording and save the Macro.

Stop macro - Dynamic Advanced Filter Excel

Once we started recording the Macro, the icon changed to stop. 

Insert new criteria value and press CTRL + SHIFT + J (our chosen shortcut)

Dynamic Advanced Filter - Dynamic Advanced Filter Excel

We no longer repeat the process from the beginning. Just press the key.  And this will provide the desired outcome.

2. Macros with Button

You can insert a Button to execute your Macro. Sometimes buttons can be more useful than a keyboard shortcut.

In the Controls section of the Developer tab, there is an option called Insert. Click that.  

Button - Dynamic Advanced Filter Excel

Now from here select the Button. A dialog box will appear where you need to assign a Macro for the Button. 

Button Setup - Dynamic Advanced Filter Excel

This dialog box will open when you click Button. Now you need to name your Macro or create a new Macro.

Button Macro set - Dynamic Advanced Filter Excel

We have a Macro Advanced_Filter, we have inserted that here. You can do that or you can record a new Macro. Then click OK 

Button Visible

We have named our Button as Filter. Feel free to set your preferred name.

Let us filter only by the product Mobile. Write the criteria value in the respective field. And then click the button.

Use the button

We have inserted Mobile in the Product Type column and clicked the button Filter. Then we have found all the rows that contain Mobile irrespective of their region or brand.

Result from button

Dynamic Filter

Apart from the Advanced Filter, you may also need any other form of dynamic filter. Where you want to explore the changes in real-time. Let’s see how can we apply that.

We will make the dynamic filter using the VBA code.

First of all, we need to format our table by using the Format as Table.

Format as Table

Select any of the formats from here.

Set table name

Make sure to provide your table a name. Here we have provided the table name as Data. 

Now from the Insert option of Controls section within the Developer tab, select Text Box.

Text Box

Insert the box at your convenience.

Text box set up

You will be in the Design Mode similar to the image above. Double click on the box. Microsoft Visual Basic for Application window will appear.

Copy and Paste the code there.

Private Sub TextBox1_Change()

Application.ScreenUpdating = False

ActiveSheet.ListObjects("Data").Range.AutoFilter Field:=2, Criteria1:= [G3] & "*", Operator:=xlFilterValues

Application.ScreenUpdating = True

End Sub

Visual Basic Window

In our code we have Criteria1:= [G3] & “*”, this is because what input we provide in the G3 cell will be our criteria. See we have set G3 as our LinkedCell. 

[G3] & “*” means that our input should be at the first and then any value can be there.

See we have inserted Ca in the box and it returned values from the Canada region.

Dynamic Filter

Using Range.AutoFilter Field:=2 we have chosen the column Range. If you need other columns change the number accordingly.

Conclusion

That’s all for today. We have tried listing ways to use the dynamic advanced filter. We have used Macros and VBA codes for reaching our target. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other ways of what we have missed here.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo