8

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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download the Excel File

Download the example Excel file to follow along.

Macro Buttons For Filters.xlsm (4.8 MB)

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.

Create VBA Macro Buttons to Apply Filters in Excel

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.

Macro Recorder to Create VBA Code for Filter Drop-down Menus Excel

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).
    Use a Shape for a Macro Button VBA Excel
  2. Add text to the shape (Right-click > Edit Text | or double-click in the shape).
    Draw the macro button shape and style it
  3. Assign the macro (Right-click the border of the shape > Assign Macro…)
    Right-click Assign Macro Excel Shape
  4. Select the macro from the list.
    Assign Macro Window for Macro Button Shape
  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.

Left Click Shape to Run Macro - Ctrl to Select Edit Shape

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.

View and Modify VBA Code in VB Editor

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.

VBA AutoFilter Method Applies Filters to Columns - Filter Drop-down Menu

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 Fitlers mentioned above, but there are a few important things to know about the AutoFilter method.

VBA AutoFilter Method Important Things to Know

  • 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.

Duplicate Macro Shape Button Ctrl Shift Drag

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.

Multiple Macro Buttons to Apply 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.

Create the Personal Macro Workbook and Add Buttons to the Ribbon

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.

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to learn more and register for the webinar

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

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 8 comments
Delville - July 26, 2018

Thank you so much Jon, very interesting, looking forward to your VBA cause tomorrow.
Cheers from Down Under

Reply
Ahmed Sheikh - July 20, 2018

Wonderful! So simple made it to understand!!!

Great job Jon! Keep it up man!

Reply
Ahmed Sheikh - July 20, 2018

Lovely style making it so simple to understand!!!!

Great job……………..keep it up jon!

Reply
jfdelvi - July 20, 2018

Very good explanation. Thank you

Reply
Sandeep Kothari - July 20, 2018

Jon, this is fabulous!

Reply
    Jon Acampora - July 20, 2018

    Thanks Sandeep! I appreciate your support! 🙂

    Reply

Leave a Reply: