Filter as you type [Quick VBA tutorial]

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!

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.

Click here to know more about the VBA classes and enroll today.

The post Filter as you type [Quick VBA tutorial] appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.