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.
[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.
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:=xlFilterValuesReplace 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.
95 Responses to “Filter as you type [Quick VBA tutorial]”
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).
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
=SUMPRODUCT(--($A$2:$A$32<=J4)*($B$2:$B$32="CELLO"))
Dear Kuldeep,
I am not understand why u are using "J4" pls clarify the same.
Thanks
Jitendra
j4 is the cell reference you just need to put date instead CELL REFERENCE.
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"))
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.
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.
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
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.
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,
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
Chris,
I detailed the formula above in an article. Let me know if you have any questions or concerns.
Chris O.
Maybe I'm just missing it, but can you please provide the article? Thanks in advance!
-Chris
Chris can you share the old sheet where you modified using the hidden column one.Can mail to balam1986@gmail.com
Bala,
I resent the link. If you just click my name you go to the share site. I would delete your email now that you know how to contact me. - Chris
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.
@Slsuer:
Can you double check? The filtering should work for occurrence anywhere.
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.
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
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
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.
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.
Did you convert your data into a table? If you created a named range instead you will likely get Error 9.
Yes, I used a table, but got the error nevertheless.
Got it: made a mistake by not using the correct table Name.
[…] http://chandoo.org/wp/2015/08/22/filter-as-you-type-excel/?utm_source=feedburner&utm_medium=emai… […]
Hey chandoo, could please explain this because I am getting run error 9 .
@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?
@hui
Thanks for your reply and I understood my problem... I have one requirement. The requirement is that I want the same filter as you type but I want to filter as you type number is that possible?
Please post the question and file in the Chandoo.org forums
http://chandoo.org/forum/
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.
@ 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
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,
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.
Slsuser,
Thanks for the tips its working
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
Hey thanks for sharing the file it's quiet interesting.I just need to understand how that if nesting work as filter
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
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.
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
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
Thanks Chris !
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
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.
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
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.
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.
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
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?
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.
This does not apply with integers/numbers? any problem with it. It cant search for numbers. Pls help
How can I change the code to allow it to search in all columns of the table??? Thank you so much!
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
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?"
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,
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
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
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!
Hi Chandoo,
can we use this also on pivot table? i tried, but failed.
Thanks
Wilber
your above code is not working it is returning error 9 thanks for nothing
Rena,
Ouch! What is your config? I have not had that experience but am always willing to improve the code. I did not error trap to keep this example simple. - Chris
I would also love to know how to reference my pivot table to use this feature!
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
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"
you make the awesome filter as I need
thank you so much, chandoo
This doesn't work for me. The text box and target cell are linked and work ok. but, no filtering is happening.
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.
The macro doesn't work with numbers filled in table
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
Hi Chris, Can you email the code and instructions. I can post it on your behalf.
Sent it over.. Did not have time to document it really well (at all). There is only one module and worksheet code from you. The color coding is just conditional formatting. - Chris
Hi Chandoo - Can you please tell me where Chris's worksheet and code is ? I am interested.
Ron,
Chandoo said he would post the code after reviewing my work. I did not have much time to refine the instructions for general consumption, so he is helping check out how I do it.
Chris
Hey Chris - All good. Thanks.
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.
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
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
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! 🙂
Can we make the filter as it types macro work for pivot tables?
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
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!
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
The macro expects a named range "search_string". Make sure you have that in your workbook.
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
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.
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/
Hi Chris,
Can you please send the code to my mail
rasen_p@yahoo.com
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!!!
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?
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".