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.
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:=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!
Want to filter multiple column table? Use this macro instead…
The above code works fine if you have just one column data. What if you need to filter a giant table with several columns? Our reader Chris thought about the problem and shared below approach.
- Create a new column at the end of your table that concatenates all column data. Something like this
=CONCAT(Table3[@[first col]:[last col]]&” “)
- Now add Worksheet_Change event (or Textbox_change event) to monitor the input cell
- Apply filter on the concatenated column. Sample code below.
Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range Set KeyCells = Range("search_string") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then FastFilter (KeyCells.Value) End If End Sub Sub FastFilter(sch As String) Dim lo As ListObject Set lo = ActiveSheet.ListObjects(1) lastcol = lo.ListColumns.Count If lo.AutoFilter.FilterMode Then lo.AutoFilter.ShowAllData lo.Range.AutoFilter Field:=lastcol, Criteria1:= _ Array("*" + sch + "*"), Operator:=xlFilterValues Else lo.Range.AutoFilter Field:=lastcol, Criteria1:= _ Array("*" + sch + "*"), Operator:=xlFilterValues End If Range("search_string").Select End Sub
Filter on any column – VBA Trick – Explanation video
Watch below video to understand how “filter on any column with VBA” trick works. You can watch it here or on my YouTube Channel.
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.
Click here to download the FastFilter code example file.
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.