Blog Archive

Microsoft Excel: Filter as you type


​​
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:
filter-as-you-type
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.
filter-as-you-type-in-excel-example-data
Step 2: Insert a text box active-x control
Go to developer tab and click on insert > text box (active-x) control.
filter-as-you-type-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.
properties-window-textbox-activex-control
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:=xlFilterValues
Replace 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.

Post a Comment