Filter as you type [Quick VBA tutorial]

Posted on August 22nd, 2015 in VBA Macros - 54 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.

54 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

    • Chris says:

      Chris,
      I know this was a long time back, but would you happen to know the formula you used to search the entire table? This is something I'm trying to do, but to no avail right now.

      Thanks,
      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

  19. Thuy An says:

    How can I change the code to allow it to search in all columns of the table??? Thank you so much!

  20. Chris says:

    Chandoo!
    This is an excellent bit of code, my only quandary is that I'd like the exact same functionality to search my entire table. So far everything works as it should to search the first column.

    The code I'm using is:

    ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=1, Criteria1:="*" & [ac3] & "*", Operator:=xlFilterValues

    If between the time you originally created this post(2015) and now, you've figured out a simple way to search the entire table using the same methodology, I'd absolutely love to see it.

    Thanks in advance for all of your Excel computing superpowers!

    Chris

    • Chris O'Neill says:

      The method above does search the entire table. You just do the work in the first column and then filter on true. The problem you have is that you can only filter on one column at a time. So you need that one column to answer the question, "Did my search have a result anywhere on this row?"

      • Chris says:

        Sorry Chris but I'm quite green in VBA. If you could explain how to formulate that loop based on what I've already got together (ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=1, Criteria1:="*" & [ac3] & "*", Operator:=xlFilterValues), it would be a huge help.

        Regards,
        Chris

        • Does the filtering part work for you? Does it filter at least one column? If you have that working you only need to add a column in the front and use the formula part to get the true false. This is more a formula thing. - Chris

          • Chris says:

            Chris,
            Yes, the filter for the first column works like a charm, no problems there. I'd just like to make it filter all other columns when I enter specific criteria.

            Thanks,
            Chris

          • Chris,

            I looked at the code last night. Allow me to explain. Your vba is this:

            ActiveSheet.ListObjects("states").Range.AutoFilter Field:=1, _
            Criteria1:="*" & [e4] & "*", Operator:=xlFilterValues|

            That sets the pull down in the filter to **. You cannot apply the filter to all columns for two reasons: First, we can safely assume E4 is one value and that it occurs in only one of the columns. It is unlikley the value in E4 appears in multiple locations on the same row. Second, a filter can only be applied to one column filter at a time. So you cannot get what you want with the filter as you type sample.

            The key line of code I shared with you is this:

            ActiveSheet.ListObjects("tb_activity").Range.AutoFilter Field:=1, Criteria1:="TRUE"

            This filters the first column to TRUE. So then the question becomes "Can the first column do some work and check for our value in all the columns resulting in one of two states "true" or "false"? The answer is yes. That is why I use the first column not for data but for checking any and all cells for the search_string.

            The pseudo code works like this for the formula: Does "dog" appear in column2? No? How about column3? No? How about column4? yes - great! show TRUE and I will filter on that. Then my vba code above will filter all rows with the word TRUE in column1.

            Does that make sense to you? If yes, you need to do the following:

            1. Add a column in your SS for the magic formula and modify it so it reflects the correct names of a.) The cell reference where you text box in linked b.) The column names or headings of the 2nd and last columns in your table.

            2. Swap the code above in your Workbook Module for my code. I highly recommend you create a new copy of your model so you can go back if you mess it up.

            If you want to, change all the data to something else and email or post your model and I will try to make the change for you.

            Chris O

  21. Wayne says:

    Your example is exactly what I wish to achieve but I have tried to follow the instructions exactly but cannot make it work.

    There is some bits missing in the code because of the width of the page but have managed to pick up the remaining letters...

    I cant tell if the end of the code is ...Operator:=x1FilterValues, ...Operator:=xIFilterValues or ...Operator:=x|FilterValues... so this probably has an impact

    Once I set the table I cannot name it... excel chooses the name which is usually Table?#... even if I name the range before I set the table it renames it to Table?#

    With the link and the code set... when I type anything into the linked cell... nothing appears in the textbox until I press enter... and then when I press enter... what I get in my text box is whatever I have typed into the linked cell.

    I am not a programmer of any form, I am simply following the instruction in the blog and I apologise for being a numpty but I work in Excel 10 and if you could send me a sample spreadsheet, this will help me out immensely.

    Thank you so kindly

  22. Katie says:

    Hi! Love this code and it works great. However is there a way I can get it to also search two words with a combination "AND" filter?

    Thanks!

  23. Wilber says:

    Hi Chandoo,
    can we use this also on pivot table? i tried, but failed.

    Thanks
    Wilber

Leave a Reply