How to Create VBA Macro Buttons for Filters in Excel

Bottom line: Learn how to create macro buttons that apply filters to multiple columns in a filtered range or Table.

Skill level: Intermediate

Download the Excel File

Download the example Excel file to follow along.

Macros for Filters

If you regularly apply the same filters to multiple columns in Excel, then you know how time consuming it can be.  In the video above it took me 24 mouse clicks and key strokes to apply filters to three columns.

Fortunately I didn't have to scroll horizontally to find the columns.  That can add additional time to the task when we are filtering a large data set with a lot of columns.   My Filter Mate Add-in helps with this task of navigating filtered ranges.

Let's see how we can turn those 24 steps into one single click or keyboard shortcut by creating a macro to filter our data.

Step 1: Record or Write the Macro

The first step is to create the macro.  In the video above I use the macro recorder to create the code.

The macro recorder creates the VBA code for all the actions we take in Excel.  It is a great tool for creating macros for filters.  The code can get complex, especially as we apply filters with multiple criteria, and the macro recorder can create most of the code for us.

Here's how to record the macro.

1. Clear the filters on your sheet or Table.
2. Turn the macro recorder on (Developer Tab > Record Macro button)
3. Give the macro a name.
4. Choose to Store macro in: This Workbook
5. Click OK
6. Apply filters to one or more columns using the Filter Drop-down menus
7. Stop the recorder (Developer Tab > Stop Recording button)

We now have a macro that will apply all the filters.  You can look at the code in the VB Editor if you'd like.  I explain more about that in step 3 below.

Step 2: Create a Macro Button

Next we will create the button on the sheet and assign the macro to it.  I like to use Shapes for macro buttons, but you can also use the Command Button control from the Insert drop-down on the Developer tab.

Here are the steps to create the macro button.

1. Draw a shape on the sheet (Insert tab > Shapes drop-down > Rectangle shape).
   
2. Add text to the shape (Right-click > Edit Text | or double-click in the shape).
   
3. Assign the macro (Right-click the border of the shape > Assign Macro…)
   
4. Select the macro from the list.
   
5. Press OK.

When you hover your mouse over the shape it will now turn into the hand pointer cursor.  Clicking the shape will run the macro.

Woohoo!  We have just turned dozens of steps into a single click! 🙂

If you need to select and move/edit the shape, hold the Ctrl key while clicking the shape.  This prevent the macro from running and allow you to modify the shape.

Step 3: Modify the Macro

Eventually you will want to modify this code to change the filters.  Of course you can re-record the macro to get the new code, but it's also good to understand how the code works.

We can look at the code in the VB Editor by clicking the Visual Basic button on the Developer tab (keyboard shortcut: Alt+F11).  Then double click the module that was added to the workbook by the macro recorder.  See the video above for details.

Each line of code uses the AutoFilter method to apply the filters.  The AutoFilter method has 5 parameters.  These parameters allow us to specify the column that the filter will be applied to, and the criteria.

I have a series of posts on this topic, starting with The Ultimate Guide to Excel Filters with VBA Macros: AutoFilter Method.  There are additional articles for different filters and data types including: blanks, text, numbers, dates, colors & icons, and clearing filters.

Important Things to Know about AutoFilter

I recommend checking out the Guide to VBA Filters mentioned above, but there are a few important things to know about the AutoFilter method.

• The AutoFilter method can be used on both regular ranges or Excel tables (VBA ListObjects).  I explain more about that in the Guide to VBA Filters post.
• Each line of code clears and applies a filter to a single column.  The code first clears any existing filters to the column, then applies the criteria specified in the line of code.  Therefore, we cannot use multiple lines of code to apply multiple filters to the same column.  All criteria for the column must be in one line of code.
• The ShowAllData method is used to clear all filters.  This line can raise an error if the filters are already cleared from the sheet.  Checkout my post on clearing filters with VBA to learn how to handle the error.
• The values for Criteria1 and Criteria2 are wrapped in quotation marks.  Any comparison operators for greater than/less than filters (= < >) or wildcard characters (* ?) are also included inside the quotes.

Give Your Users More Buttons

Once the users of your spreadsheet use the button, they will want more of them!  Fortunately it's easy to replicate the button.

Step 1: Make a copy of the macro button

The easiest way is to hold the Ctrl key, then select the shape, then drag it right or left.  This will duplicate the shape.

If you hold the Ctrl and Shift keys during the process it will keep the new shape aligned with the original.  Here's a quick screen cast animation that shows how to duplicate the shape.

Step 2: Create a new filter macro

You can record a new macro with different filter criteria.  Or, you can copy the existing macro in the VB Editor and paste it below.  Just remember to change the name of the macro and also modify the AutoFilter lines of code.

Step 3: Assign the macro

Assign the new macro to the new button.  You can continue to replicate this process for commonly used filters.

The screenshot above shows an example of 5 macro buttons that apply different filters.  I made it look like a slicer, but it's actually shapes assigned to macros.  The file that contains this example is the file on the The Ultimate Guide to Excel Filters with VBA post.

Your users (co-workers) will love the time-saving convenience of these buttons, so expect some high fives coming your way. 🙂

Adding Macro Buttons to the Ribbon

We can also add macro buttons to the Ribbon toolbar in Excel.  Checkout my 4-part video series on the Personal Macro Workbook to learn more.  In that series I explain how to add custom buttons to the ribbon for use on any open workbook.

Those ribbon buttons won't necessarily travel with the workbook, unless you use Ribbon XML to modify the workbook.  I'll do a follow-up post on the differences between those techniques.

Free Webinar on Macros & VBA

If you are interested in learning more about macros, I'm currently running my free webinar called “The 7 Steps to Getting Started with Macros & VBA”.  It's running all next week, and it's absolutely free to register.

During the webinar I explain why you might want to learn VBA, and a lot of the basic coding concepts that will help you get started.  I jump into Excel and the VB Editor and walk through how to write and run our first macro.  Even if you have been using VBA for awhile, I'm sure you will learn some new tips.

Click here to learn more and register for the webinar

Please leave a comment below with any questions or suggestions.  Thanks! 🙂