Filter as you type [Quick VBA tutorial]

Posted on August 22nd, 2015 in VBA Macros - 41 comments

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.

More awesome ways to filter your data

If you are often filtering your data, you will find below tips handy:

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.

41 Responses to “Filter as you type [Quick VBA tutorial]”

  1. Rahul says:

    I am blank in Macros, tried copying the code. But got Run-time error:9. Please help and also suggest some resources to grab the basics of Macros (for beginners and intermediate).

  2. Jitendra sharma says:

    Dear Chandoo,

    Please help me.

    I want to know how many times visit in a company within a week & within a day.

    DATE Visiter Name
    01-Aug-15 JVS
    01-Aug-15 FAZE
    01-Aug-15 TREND
    01-Aug-15 SHAHI
    03-Aug-15 WAZIR
    03-Aug-15 JVS
    03-Aug-15 CELLO
    03-Aug-15 HANUNG
    03-Aug-15 WAZIR
    04-Aug-15 TRIDENT
    04-Aug-15 ANIKET
    05-Aug-15 JVS
    05-Aug-15 DESIGNCO
    05-Aug-15 ABHITEX
    05-Aug-15 JVS
    05-Aug-15 CELLO
    05-Aug-15 SHAHI
    06-Aug-15 JEWEL
    06-Aug-15 JEWEL
    07-Aug-15 TREND
    07-Aug-15 NAVNEET
    07-Aug-15 KAPOOR
    07-Aug-15 TREND
    08-Aug-15 RAMESH
    07-Aug-15 ALOK
    07-Aug-15 NAVNEET
    08-Aug-15 FAZE
    08-Aug-15 SHAHI
    08-Aug-15 LUXOR
    08-Aug-15 WELSPUN
    08-Aug-15 ABHI

    I want to count.

    It means "cello" visiter visit in office 2 times with 1st to 8th Aug-15.

    Cello 2

  3. Chris O'Neill says:

    Chandoo,

    This is a great tip. I modified it to search all columns in a data table and filter if any column shows the value. I was using conditional color coding but this is much nicer. Let me know if you want the sample sheet. - Chris

    • Chandoo says:

      Hi Chris,

      That is lovely. Please post the VBA code in comments or email me at chandoo.d@gmail.com. I would like to learn from it and share it with rest of the readers.

      • Chris O'Neill says:

        I sent you the model - there is no VBA code. I use only your code for the filter as you type. I created a formula means to create a hidden column where it searches all the other columns. Brute force but works like a top. I also ended up bailing on the filter as you go when the database got too big. Now I just have a running total and click that box to filter once. Please let me know if you have any questions or concerns. Great site, nice people in your forum. happy to share what little I Know.

        Chris

  4. slsuser says:

    This is great but seems to only filter on the the starting characters in the strings.
    Can the code be changed to a sequential search to show the results where the combinations entered appear anywhere within the string?
    For example, using the original data, if I type in "or" then florida and georgia would display in the list.

  5. slsuser says:

    Thanks for your reply....
    Apologies, it may be working.
    Having issues with Excel right now and can only open older version. Will check next weekend upon arrival of new pc.

  6. Tim says:

    Chandoo,

    This is a great method. I have one question though.

    Say I want to filter by column "region" in the "states" table how would I go about doing that? I tried replacing "states" with the column name "states[[#All],[Region]]" and "states[Region]", but to no avail.

    Thank you!
    Tim

  7. Lloyd says:

    Awesome as always,
    I was thinking about trying to us something along these lines to replace a slicer (for a very long list) in a pivot table. My initial thought were;

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Items").Range.AutoFilter Field:=1, Criteria1:="*" & [a1] & "*", Operator:=xlFilterValues

    But this failed abysmally with a 438 error.

    Any thoughts on if this is possible and the correct syntax to make it work?

    Thanks again

    Lloyd

  8. Rick says:

    Chandoo,

    Excellent example. You state "To keep it simple, let’s assume your values are in an Excel table". Must the data be in an Excel table? I can't seem to make it work otherwise.

  9. kasawubu says:

    Dear Chandoo,

    this is a great tip.

    Tried to use it in an example on my own (Excel 2013).
    Replaced name and linked cell, but always get vba error 9 'Subscript out of range'.

    Tried different things already - no success.
    How can I make it work? Thanks.

  10. nikhil says:

    Hey chandoo, could please explain this because I am getting run error 9 .

    • Hui... says:

      @Nikhil

      Run Error 9 is usually because you have used a variable outside its defined range or have tried to use a variable which hasn't been defined
      eg: Setting an Integer variable to more than 32,767
      or trying to use a variable which hasn't been defined
      eg: defining a workbook as a name and then referencing the work book when it isn't opened

      Can you post the file or VBA Code in question and highlight where it is failing?

  11. slsuser says:

    to Nikhil...
    To me it appears that the numeric as not filtering because they are numeric. I entered a number as 65432 and with a leading ' (single quote) to cause the number to be entered as text and the filter works.
    that would be a lot of effort.
    I then formatted the table as text instead of general. New numeric entries added to the end of the table were treated as text and filtered properly.
    I see the bets way would be to convert all entries to text in the VBA but I haven't figured that out. Formatting the E4 value as chr fails on text. Perhaps need to inspect the value to see if numeric and then process differently if numeric?? not sure but this is food for thought.

    • nikhil says:

      @ slsuser
      Thanks for your for reply
      I already tried converting numeric to text by using both the methods as u mentioned but still the filter is not working????. Could please share the code wic u used it
      Thanks in advance

    • nikhil says:

      @ slsuser
      Thanks for your for reply
      I already tried converting numeric to text by using both the methods as u mentioned but still the filter is not working????. Could please share the code wic u used it
      Thanks in advance

      • slsuser says:

        Nikhil,
        No coding is required. You must format the column as TEXT before inputting the values. The table in the example spreadsheet (filter-as-you-type.xlsm) is formatted as "General" so numbers are treated as numbers and alpha is treated as text which is why the filter fails on only numeric values.
        Simply select your entire table and change the format to text. All NEW entries (at the bottom of the table) will be formatted as text.
        Existing entries will NOT automatically change to text. If there are not to many you can go to each cell and press F2 and enter and the number should change to Text format.
        I have tried this and it works without coding.
        Note if you plan to perform any numeric functions you will need to convert back to number format in your formulas.

  12. Hey, I liked the search but wanted to be able to have all columns searchable.

    1.) I use data table.
    2.) I created column that does a find of the string against each of the columns. (I hide that column normally)
    3.) I use Chandoo's code to set that search column to 1 which is the value given when any of the other columns find the string or any part of the string.

    I posted a sample doc to google here:

    https://drive.google.com/folderview?id=0B3NPE3t4gC36WDMxWnpSS1EtbkE&usp=sharing

    Hope this helps someone as much as the tip helped me.

    Chris

    • nikhil says:

      Hey thanks for sharing the file it's quiet interesting.I just need to understand how that if nesting work as filter

      • Chris says:

        N,

        Okay - here is how the nesting works.
        Find looks for the string inside a column
        If it finds no string an error is thrown
        I use iferror to trap the error and make a 1 if no error exists and zero if it does exist
        Thus the string comes from the dialog box and creates a 1 for each column that contains the text in the dialog box
        I add all the 1 's together and then creat a formula that says if the total of the 1 's. Is greater than zero put. 1 or true in the cell to the left

        Then I use the filter aa you type but filter on 1 or true.

        I hope this helps. I use this for over 600 paragraphs across 10 columns and it works well.. If a little slow....

        Chris

  13. JM says:

    Hello.. what if i want to filter a multi column table? because it only filters the first column of a table? how could refer to the other column? thanks

    • slsuser says:

      JM, see Chris O'Neill comment and example on 9/13/2015. I have not tried it myself but the logic appears proper. Let us know if that works for multi columns in your environment.

    • Chris O'Neill says:

      I will send Chandoo the model and let him break it down for you. Essentially, I am doing a boolean search using a formula in one cell. Then I am applying Chandoo's search on the on column. So col1 = yes or no, column 2 = yes or now, column three yes or no. If yes equals 1 and no equals two then the total would be zero if nothing is found and more than zero is something in any column is found.

      Chris
      conw88@gmail.com

  14. sherif says:

    i have create this filter as you type file for my 10 field table.

    it is working fine but i have numeric field as well, which is not getting
    filter with this vba code. can you provide vba code of numeric as well.

    thanks in advance.

    • sherif says:

      i wanted to tell you one thing my data is copied from word to excel,

      even if you converted in to text. this format is not picking up.
      then i have double click on each cell. after double click it is getting convert to text and left side corner i am getting a small green dot.

      if i click on it, it is giving an option convert as number.

      it is very difficult to each on cell because i have almost 9000 rows.

  15. Jdb says:

    I changed the field to 4, which allows it to search all the columns in my table. However, I have duplicate values that I want to show when searched, but for some reason the search narrows it down to only one response.

    Is there a way to get it to show all duplicate values?

    The code I have for my table is this:

    ActiveSheet.ListObjects("RegulationSearch_tbl").Range.AutoFilter Field:=4, Criteria1:="*" & [H1] & "*", Operator:=xlFilterValues

  16. Brian Skinnell says:

    Chandoo,
    Let first start off by saying that I love all of the AWESOME content that you provide to us Excel-nerds!! Keep up the great work and I hope that you're enjoying New Zealand.

    Second, I have a question regarding on this particular subject. I have created a spreadsheet that I can filter on 7 different colums and a button to clear all text box filters and it works AWESOME!! However, one of the columns does may not have a value on every row and when I clear (i.e. backspace) what I've typed in the text box filter for that particular column, it does not redisplay the rows with no value. The good news is that the button that I created solves the issue but was wondering if there is a fix for this?

  17. Mamun says:

    I think something, I am missing in" Filter as you type [Quick VBA tutorial]" . Because I am tried many times in my own project but I faild. Will you detail description on Filter as you type [Quick VBA tutorial].

    Thanking You.

  18. wobie says:

    This does not apply with integers/numbers? any problem with it. It cant search for numbers. Pls help

Leave a Reply