fbpx
Search
Close this search box.

Filter as you type [Quick VBA tutorial]

Share

Facebook
Twitter
LinkedIn

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.

  1. Create a new column at the end of your table that concatenates all column data. Something like this
    =CONCAT(Table3[@[first col]:[last col]]&” “)
  2. Now add Worksheet_Change event (or Textbox_change event) to monitor the input cell
  3. 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:

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

95 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

    • Kuldeep Mishra says:

      =SUMPRODUCT(--($A$2:$A$32<=J4)*($B$2:$B$32="CELLO"))

      • Jitendra sharma says:

        Dear Kuldeep,

        I am not understand why u are using "J4" pls clarify the same.

        Thanks
        Jitendra

        • Kuldeep Mishra says:

          j4 is the cell reference you just need to put date instead CELL REFERENCE.

          • PRADEE PRAJAPATI says:

            Hi Kuldeep,
            I am getting this output "08-Aug-15", "-3" when i use this formula- =SUMPRODUCT(-($I$3:$I$33<=I35)*($J$3:$J$33="SHAHI"))

    • Hamid MilaniNia says:

      Have you tried to utilize PowerQuery utilizing converting the range into a table then bring it in Power Query, Split the columns by selecting the first 9 characters from left, rename the columns to Dates, Name then bring the table back into Excel, Insert a Slicer select name as a filter. Simply click on the name and the table displays only days which the Visits where made.

    • Power by PK says:

      Hi,
      Jitendra Ji

      Simple
      If Date and Name (B2:B20) in separate column..

      Type a visitor name (D2) in outside of table and use formula
      =countif(B2:B20,D2)

      Reply if formula work as your think.

  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

    • Bala says:

      Chris can you share the old sheet where you modified using the hidden column one.Can mail to balam1986@gmail.com

  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

    • Ron says:

      Hi Chris,

      I would be very happy if you could please post your sample doc to Google drive once again. I am looking for exactly what you are describing.

      Thks.

      • Chris O'Neill says:

        Hi Ron,

        Per your request, I am reposting the link to the article and model I wrote.

        https://drive.google.com/drive/folders/0B3NPE3t4gC36OWVBT2lsNzB0LWM?usp=sharing

        Since then, I have moved on to different approaches to the problem as this solution starts to slow down as the database grows. If you are good with VBA I can share some functions and subroutines that filter tables quicker than this way. - Chris O

        • Ron says:

          Chris,

          First impression - very good. I dumped 60,000 records that have 23 columns (1.4 million cells). Yes, it currently only checks the first 5 columns, but it is very fast. No - Unfortunately, I am not good with VBA.

          This does serve a purpose for me. However, what I am ideally looking for is something where I can have the ability to do multiple search & filtering by column across the board.

          Can this be more or less easily modified to adapt to what I am looking for ?

          Many thanks for the quick posting Chris. I took a chance that you would respond after your original post from a couple of year's back.

          Ron

          • You probably have a decent processor and ample memory. I am happy you get value from this.

            You can modify it easily to check all the columns. Look at the formula in the first column... replace the text "column2" with the exact text of your first data column... then replace column5 with the exact name of your last column. I like to create a column on the end of my table called "LastColumn" then hide it which means the formula will always work if I use the same names.

            =NOT(ISERROR(MATCH("*"&IF(ISBLANK(search_string),RAND(),search_string)&"*",TEXT(tb_main[@[Column2]:[Column5]],"0.00000"),0)))

          • I updated the file to reflect the changes. A few tips and tricks:
            1.) Cope and paste the formula to notepad, clear the whole search column and paste it in the first cell which will create a clean uniform set of searches.
            2.) Note that you will get a cell error warning if your last column is full of blank cells. This is not an issue and will not effect anything but the aesthetic of the warning green triangle.

            Chris O

          • Ron says:

            Thanks 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

  24. rena campbell says:

    your above code is not working it is returning error 9 thanks for nothing

  25. April Rommel says:

    I would also love to know how to reference my pivot table to use this feature!

  26. Lazar says:

    This is great. Can u please help me to put syntax for range not for table object. For example only in column A.

    Thank you

    • Chris O'Neill says:

      Why just covert the range on the fly to a table?

      Dim src As Range
      Dim ws As Worksheet
      Set src = Range("B5").CurrentRegion
      Set ws = ActiveSheet
      ws.ListObjects.Add( SourceType:=xlSrcRange, Source:=src, _
      xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleMedium28").Name = "Sales_Table"

  27. chatp Tun says:

    you make the awesome filter as I need
    thank you so much, chandoo

  28. David Gitson says:

    This doesn't work for me. The text box and target cell are linked and work ok. but, no filtering is happening.

  29. Mustafa N says:

    Works great! However I am also having the issue on some columns where you type, it filters properly, but when you clear the search box it does not bring back the rows with blank entries. I have this filter in use on 44 columns. This issue is present in about half of the columns. I'm not sure what the difference is between the working columns and the non working columns. The code and properties are identical aside from assigned column.

  30. NADIR says:

    The macro doesn't work with numbers filled in table

  31. Chris says:

    Chandoo,

    I wanted to post a new variation on the fast filter I have been using for the last two years. Where can I post that for folks?

    Chris

  32. SS says:

    Hello,

    I am very new to coding in Excel and need to apply this to a work document. Could someone please tell me what sections of Chris' code I would need to change to reflect my own data, if any? I am totally clueless with this stuff and I would greatly appreciate any help!

    Thank you.

    • Chris says:

      Hi SS,

      Since you are new - first thing you need to do is make a copy with SAVE AS and then read on.

      Your data needs to be organized in a table. If it has rows and columns and looks squarish you can put the cursor in the middle of it and use CTRL-T to make it a table. Then you follow the process I posted. Otherwise find a way to share the data file with Chandoo and I will take a look at your stuff.

      Chris O

      • SS says:

        Hi Chris,

        Thanks so much for your response! I really appreciate your help. The only means I see of sharing the data file with Chandoo is through email. Unfortunately, it states the Chandoo email account is only checked once a month. Too bad I am not able to attach the file directly with my posting. I'll try to thoroughly explain the file in hopes you are still able to help.

        My data is in Table1 cell range A8:U8 and data is continually being added below row 8.

        My ActiveX Text Box is named textbox1 and is linked to cell D3. Below is the code I'm using.

        Private Sub TextBox1_Change()
        ActiveSheet.ListObjects("table1").Range.AutoFilter Field:=1, Criteria1:="*" & [d3] & "*", Operator:=xlFilterValues
        End Sub

        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

        Thank you again Chris and I'll keep my fingers crossed that the issue can be resolved easily!

        -SS

  33. SS says:

    With help from some new friends, I was able to accomplish the correct code necessary to have an Excel file dynamically Auto-Filter with all columns in a data table. I wanted to share the code below, in case someone like myself (no VBA experience) needed to accomplish the same task for the sake of their job as well.

    1. Ensure your data table is set as a table with a name (default name is Table1). You should be able to select it in the upper, right corner below "File".
    2. Create a Textbox under Insert ActiveX Controls via Developer Tab.
    3. Right-click on the textbox and view the code.
    4. Enter the code below and change the names of all parts of the code in CAPS, to match your Excel item names.

    Sub ClearAllFilters()
    ActiveSheet.ShowAllData
    End Sub

    Private Sub TEXTBOX1_Change()
    With ActiveSheet.ListObjects("TABLE2") ' Enter the name of the "Intelligent Table" in the worksheet

    IntTabColumn1 = .DataBodyRange.Rows(1).Cells(1, 1).Column ' First column of the Find table
    IntTabRow1 = .DataBodyRange.Rows(1).Cells(1, 1).Row ' Find the first row of the table

    IntTabRowCount = .Range.Rows.Count - 1 ' Rows of the table minus the letter
    IntTabColumnCount = .Range.Columns.Count ' Columns of the table

    IntLastRow = IntTabRow1 + IntTabRowCount - 1 'Last line
    IntLastColumn = IntTabColumn1 + IntTabColumnCount - 1 ' Last column

    End With

    If TextBox1.Text = "" Then
    Rows(IntTabRow1 & ":" & IntLastRow).EntireRow.Hidden = False
    Else
    new_filter (TextBox1.Text)
    End If
    End Sub

    5. On the left-top corner in the project "tree", right-click anywhere under your project name or "Modules" and click "Insert" "Module". Enter the code below and again change the names of all parts of the code in CAPS, to match your Excel item names.

    Public IntTabRow1 As Integer ' Variable 1. Line
    Public IntTabColumn1 As Integer 'Variable 1. Column
    Public IntTabRowCount As Integer ' Variable number of rows
    Public IntTabColumnCount As Integer ' Variable number of columns
    Public IntLastRow As Integer 'Variable last line
    Public IntLastColumn As Integer ' Variable Last column

    Sub new_filter(critere As String)
    Dim col As Integer, lgn As Integer, lignes(158) As Integer, a As Integer, find As Boolean ' "158" are how many columns there are in the table.
    a = 0: find = False

    '
    'IntTabColumn1 = ActiveSheet.ListObjects("Tabneu").DataBodyRange.Rows(1).Cells(1, 1).Column
    'IntTabRow1 = ActiveSheet.ListObjects("Tabneu").DataBodyRange.Rows(1).Cells(1, 1).Row
    '
    'IntTabRowCount = ActiveSheet.ListObjects("Tabneu").Range.Rows.Count - 1
    'IntTabColumnCount = ActiveSheet.ListObjects("Tabneu").Range.Columns.Count
    '
    'IntLastRow = IntTabRow1 + IntTabRowCount
    'IntLastColumn = IntTabColumn1 + IntTabColumnCount

    For lgn = IntTabRow1 To IntLastRow
    For col = IntTabColumn1 To IntLastColumn
    If LCase(Cells(lgn, col).Text) Like "*" & LCase(critere) & "*" Then Cells(lgn, col).EntireRow.Hidden = False: Exit For
    If Not (LCase(Cells(lgn, col)) Like "*" & LCase(critere) & "*") And col = IntLastColumn Then Cells(lgn, col).EntireRow.Hidden = True
    Next col
    Next lgn

    End Sub

    That should be everything you need to accomplish a keyword search-box to filter out all rows of data that do not include your keyword entered into the textbox! Hopefully this helps many others and saves them a lot of unnecessary, wasted time. Enjoy! 🙂

  34. Slsuser says:

    Can we make the filter as it types macro work for pivot tables?

  35. Slsuser says:

    I would be interested to know if the method similar to this would work with pivot tables. For example let’s say I have a list of customer invoice numbers which could be in the thousands with cities and or ZIP Codes in the amount of money that we have charge them.

    I realize you could do this with slicers but to put invoice numbers in slicer should be scrolling and scrolling and scrolling So the search while typing would be an excellent alternative for the pivot table if possible

    • SS says:

      Hi Slsuser,

      Unfortunately, I don't know much more about VBA or pivot tables because I never really had to use them before. So, maybe one of these other much more experienced members could answer your question. Sorry I'm not too much more help!

  36. Jason says:

    Hello Group

    I am using Excel 2013 and I am going the way of using concatenate to combine all of the date in my rows in the last column. I have my data set as a table and used the VBA code staged below; however, I a am getting run-time error '13': Type Mismatch.
    I went to debug and the following is highlighted yellow:
    FastFilter (KeyCells.Value) , I am very to new to VBA and wondering if someone could direct me in the right direction to correct this....I am thinking it might be a formatting issue? If this has been addressed already in a previous post, my sincere apologies. Thanks in Advance!

    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

    • Chandoo says:

      The macro expects a named range "search_string". Make sure you have that in your workbook.

      • Jason says:

        Thank you Chandoo! I appreciate the assistance. You were correct, in the conditional formatting rule, I totally forgot to input the equal sign and only entered Search_String! Thanks Once Again !!!!!!!

        -JV

  37. Hello Chandoo,

    I've been searching for a solution like the one presented on "Filter on any column" VBA trick.

    I have followed all steps, array formula w the &" " at the end

    My code

    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

    Module
    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

    I will note that I have not recorded any Macros, but just typed both scripts in. When I try and filter nothing it's happening.

    My main goal is to be able to type a keyword and filter/populate the information related to it

    Please help.

  38. Kadr Leyn says:

    Very thanks for tutorial. Good to have you Chandoo.

    I created too a template to filter on the worksheet based on the searched value (letters, numbers or other characters) in textbox , using the VBA Find and AutoFilter methods.

    The filtered cells are listed on the worksheet, the number of filtered cells is informed to the user by msgbox.
    I hope it benefits to users.

    Source,example file : https://eksi30.com/searching-data-in-a-worksheet-using-vba-find-autofilter-methods/

  39. Rasendra says:

    Hi Chris,
    Can you please send the code to my mail
    rasen_p@yahoo.com

  40. Arpit Thakral says:

    Hello Chandoo,

    I need to create a drop down list in excel that has autofill feature and can select multiple values separated by comma.
    please help!!!

  41. Camie says:

    I have used your fast filter code and it works great. However, it only filters one word/criteria at a time. I would like to be able to filter for any of the words in the key cell value. For e.g. if I have 'bank book' as the key cell value it should filter for all cells that have either bank or book. can your code be adjusted to filter for multiple words?

  42. Camie says:

    The FastFilter works great. However if I put "the cow" in the search field it returns only results with the phrase "the cow". I want it to return results that have "the" or "cow" or "the cow".

Leave a Reply