Filtering a list is a powerful & easy way to analyze data. But filtering requires a lot of clicks & typing. Wouldn’t it be cool if Excel can filter as you type, something like this:
Let’s figure out how to do this using some really simple VBA code.
Filter as you type – VBA tutorial
Step 1: Set up a list with values you want to filter.
To keep it simple, let’s assume your values are in an Excel table named States.
Step 2: Insert a text box active-x control
Go to developer tab and click on insert > text box (active-x) control.
Insert this control on your spreadsheet, preferably above the states table.
[Related: Introduction to Excel form controls – article, podcast]
Step 3: Link text box to a blank cell.
Click on properties button in developer tab and set linked cell property of text box to an empty cell in your worksheet. I set mine to E4.
Step 4: Add CHANGE event to text box
Right click on the text box and choose “view code”. This will take you to Visual Basic Editor (VBE) and creates an emtpy textbox1_change() event.
Quick: What is an event?
Answer: An Event is a macro (VBA code) that runs when a certain condition is satisfied. For example, textbox1_change event runs whenever you change the textbox value (ie type something in to it, edit it or delete its contents).
We need to write VB code to filter our table (states), whenever user types something in to the text box. This code is just one line!
You can use below code or come up with your own version.
ActiveSheet.ListObjects("states").Range.AutoFilter Field:=1, Criteria1:="*" & [e4] & "*", Operator:=xlFilterValuesReplace the words states and e4 with your own table name & linked cell address.
That is all. Close VBE and return to Excel.
Step 5: Play with filter as you type macro
If you are in design mode, exit it by clicking on “design mode” button in developer tab.
Click on text box and type something. Your table gets filtered as you type, just like magic!
Download filter as you type example macro
Please click here to download filter as you type example workbook. As a bonus, the download workbook as code to clear / reset filters too. Examine the code to learn more.
More awesome ways to filter your data
If you are often filtering your data, you will find below tips handy:
- Filter a table by combination of values quickly
- How to use advanced filters to extract values that meet multiple criteria in one go
- Using report filters in Excel pivot tables
- Slicers – filters for the new generation – how to use them?
- Filter odd or even rows only
Awesome as you learn:
There is no doubt that you will get awesome at your work by learning new & powerful ways to do it.
If you want to learn how to use VBA to automate your work, please consider our online VBA classes. This comprehensive program teaches VBA macros from scratch to advanced level thru step-by-step video tutorials.
Click here to know more about the VBA classes and enroll today.
