Auto Filter as you type for numbers with VBA

Hello.

I'm using Excel 2010.

I need an auto filter "as you type" that can work with numbers using VBA. In the attached file, I have two columns: Column A with numbers and Column B with text. I also have two text buttons. The idea is that when I type inside any of those buttons then the data will be automatically (in real time with no need to do anything else) filtered and show the matching rows.

At the moment, I have VBA code working flawlessly for column B, the one containing text. But I cannot make it work for sorting numbers. I have seen some ideas using a helper column but I am trying to avoid that. The existing working code for text does not need a helping column.

Please see attached example.

If there is no other way to pull this off but using a helper column, then can you please modify the excising VBA code used for column B in the attached file? I like this code since it is easier to modify than all other examples I've seen and it works flawlessly.

In advanced, thank you much.

Please Login or Register  to view this content.

Using your own methid:

Please Login or Register  to view this content.

I am assuming you want to when typing 1 filter 1 , 10, 11, 12, 13, 14 etc
Not possible without a helper column...
If you convert all your numbers to text then this will work...

Please Login or Register  to view this content.

The greater than or equal as I posted works but yours is better

Pl see file. Code is working.
Code

Please Login or Register  to view this content.

How can I make the following solution find what I type anywhere in the cell? For example, if I type 2 then 2, 12,22, 32, 42, etc.

Here is the code as provided by sintek (thank you sintek) which works flawlessly (filtering is only matching strings that begins with x criteria) but I need it modified to find what I type anywhere in the string and not just at the beginning of the string:

Please Login or Register  to view this content.

In advanced, thank you so much.

@kvsrinivasamurthy code does that

need an auto filter "as you type" that can work with numbers using VBA

This is not possible if entry is made in a cell. There is no event trigger while typing . Triggering is possible after entry of data.

I copied your code and it does what the OP wants

Here is the code as provided by sintek

Please Login or Register  to view this content.

Originally Posted by Keebellah

I copied your code and it does what the OP wants

He is telling instead of textbox cell is to be used.
When fully entry is done in cell confirmed with Enter key. code works. what is required is this.
I want to enter 21 in the cell.
2 is typed and 1 yet to be typed , in that condition code should work for 2.

I imagine OP has just made use of incorrect terminology...

For the sake of clarification, I am attaching here the file.

I used the super compact code provided by sintek for both column A and B.

I added the letter "x" to some of the cells in both columns to illustrate the point.

For example, column A is only numbers but now some of the cells also have a number with the letter x in it. Therefore, when I enter in the search box the number 2, it should auto filter to: 2, 12, 20, 21x, 22... (all the 20s) 29, 32, 42, etc.

In column B, I also added to some cells the letter "x". So if I enter in the search box the letter "a" then it should auto filter all cells containing "a" including the ones that have the x. Like this: a, aa, aba, abc, abccx... etc.

However, the formula provided by sintek is only displaying all the number that BEGIN with 2 or all the ones that BEGIN with "a" but it excludes all other cell that have the "a" anywhere else in the cell. Alternatively, If I only type "x" in either of the search boxes, it should show all the cells that contain the "x" criteria but it does not because the x is not at the beginning of the cell.

Sintek formula works by finding the search criteria in any position of the cell ONLY when I used that VBA code with one column and one column only. When I try to use it (adapt it) to the other columns, then it only finds the search criteria when it is at the beginning of the cell data.

I hope I make more sense now.

I need the code to filter all rows according to what I type in the search bottoms regardless if the search criteria is found at the beginning of the cell entry, in the middle or the end or any where. I need the code to find it anywhere, in any position.

NOTE: the code provided by kvsrinivasamurthy works. However, I am here referring to the code provided by sintek because it is so small and compact, much easier to modify. Thanks so much for your patience and understanding.

Well, we'll see, and x is not numeric so won't show in column A!!!!

see #12
With ... End With is not necessary.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I hope I make more sense now.

Post 12 solved...As well as VenA's simplification...

THANK YOU !!!

The solution provided by Vraag en antwoord works.

However, at the last minute I noticed one thing: Using Vraag solution, if I add one more record (a new entry) to the table and then I enter a search criteria in one of the search buttons, that last record gets erased ??? I need the tablet to be able to grow. In other words, if I add on row 48 the number "43" in column A or a text sequence in column B and it will get erased the minute I try to search for anything. The table will grow with new data obviously. I forgot to say that. I apologize.

Doesn't happen with my file, just tested it

You have to put the data in the table and not under it.

V&A: Funny you said that but that is just what is being done, the data is not deleted, it's just filtered away, if you clear the filter it reappears

Ok.

Let me illustrate step by step.

Please see the two files attached.

The first file is using the “long” vba code which works, but uses a helper column. Also, the number column does not need to be set to “text”. It converts the column to text and back to numbers “on the fly” which makes the code slow, but it does work.

The second file is using the short vba code which does not need a helper column and it does work when filtering BUT is “eating” away new row entries.

So, with the file that uses the long vba code and that works:
-go to cell B47 and select it, then hit the TAB key (this will automatically add a new formatted row below)
-enter (for the sake of this example) the number 44 in the newly formatted cell A48 and “aabcx” in cell B48
-select cell B48 and press the TAB key again. A new automatically formatted row will be added.
-select cell B49 and add the number 45 in cell A49 and “aacx” in cell B49.
-Now, go the “filter as you type” button for the number column and enter any number to filter by. You will see that the results obviously change as you change the filter. Do the same for the second button.
-Finally, clear the filter buttons and then look at the last two rows that were added. They are still there.

With this long vba code, I can also click on any row header and insert any new row(s) to the table, and after sorting and clearing the sort filter, the inserted rows are still there. Deleting rows does not ruin the execution of the filter either.

NOW, with the short code, any new rows added (in any way) to the table will simply disappear (the row gets hidden) after using and clearing the button filter.

Open the second file which uses the short vba code and add a couple of new rows and input data into those new rows, just as it was done with the long vba code file. Then input ANY search criteria into the filter buttons and then clear the buttons. The new rows are gone (hidden actually - but they should not be). The suggestion was to simply clear the filter and the rows would reappear (unhide), but what I need is to clear the filter as you type “buttons” and then things should show up as expected, just as they do with the first file. Autohiding rows should not be part of this equation.

As you can now see, when filtering, all rows will disappear and only the ones matching the search criteria will be shown. When clearing the search criteria all data should be back, all rows should be shown. With the short vba code, the newly added rows get hidden away permanently even if the new rows have the data which is being requested by the filter.

By the way, if I simply go to cell A48 and enter a number there, after I press enter the new row gets formatted automatically. I say this because the comment was that I “have to put the data in the table and not under it” but that is exactly what happens if I add a row to the table or if I simply put new data in the row immediately below the table (it gets formatted automatically – at least it does at my end and it has always happened that way).

I hope I explained it better.

The issue arises because the new entry 45 is entered as a number and not text...
First format your Column A to text and enter number as text (apostrophe before number)'45 ... that will solve...

The code in first workbook performs two loops...first converting data to text in order to do your required filter then restores it back to number afterwards...

Glad you got it sorted...

Since many commented and offered solutions, try this one, works for me in various tables and more. I always use a hidden column for the filtering, normally I don't use textboxes just a cell where anything can be typed but okay, me leave it and use the Textboxes and there is no need to change the cell formatting to string leave numbers as numbers strings as string.

Keebellah, I tried your suggestion with the concatenated hidden helper column and it works flawlessly. I see why you use it. It is also easy to adapt when adding future buttons. Thank you. I tried adapting it to my project but it became slow because the Excel file has at least five thousands rows. Ouch! right?!? The computer where this code is being used has 16GB of RAM, an i7 CPU with Windows 10 and it's still very slow to execute.

I may have to settle for the slowness of excel to execute this code. I really like that I don't have to change the cells to "text" or anything.

Keebellah, the code you and kvsrinivasamurthy provided does not auto hide anything. They work. kvsrinivasamurthy code, however, adds a zero to all empty cells in the column after clearing the filter criteria (I can live with that, but Excel also takes time executing it). Your code adds nothing and hides nothing, but Excel takes the longest to apply (because the file has thousands of rows).

That being said, the short vba code provided by sintek is working (after setting the properties of the cells to "text") except for another "one last" issue: if there are empty rows it automatically hides them after clearing the filter. I need nothing to be hidden. I need everything back to how it was before searching for anything. Of course, if I type zero or a dummy entry then nothing gets hidden, but I need it empty, blank.

Can any one please see the attached file and help with this? I already added some empty rows (in different places). Simply enter anything to search for in either one of the buttons, then clear the search buttons and you will see that all empty rows are hidden.

I apologize for persisting with this short vba code instead of the other two solutions (which already work), but this short code works fast with the big file I have AND is only this one issue causing trouble. Note: I need to add empty rows to have them ready for input. Some of my colleagues do not know Excel but they are kind to help input the data.


Please?

Thank you so much.

Give it a try with advanced filter.

Please Login or Register  to view this content.

It is slow because you want to filter on numbers as well as on text.

You could try adding the red llines of code (or replace the code with this code)

Please Login or Register  to view this content.

I've attached the file with modified code and a button to clear both textboxes

Upgrading your system a bit?
I briefly added to the table to ± 15000 rows with random numbers between 1 and 10000
everything is handled well within a second.
0.1015625 add 1 to filter
0.09375 add 2 to filter
0.0859375 add 3 to filter
0.0859375 add 4 to filter
0.1015625 removed 4
0.1171875 removed 3
0.375 removed 2
0.65625 removed 1

Another possibility...

Please Login or Register  to view this content.

Try this...Column E is General and not Text...Snippets taken from jindon

Please Login or Register  to view this content.

Or for VenA code ...just add below to speed up process...

Please Login or Register  to view this content.

Seems your initial requirement has changed...
Are you wanting to filter..
Name then Last Name then Phone then ID having them all work in sequence...If so that seems excessive...
Granted you might have a database with same name & surname, but telephone number or Id...Doubt it...

Well, this is a clear example of what starts as a simple how to an extensive solution

I think we should back to the beginning.
The first thing and using the entire tabel you have posted as Final Test:
WHAT IS IT YOU REALLY WANT?
Maybe I'm dense but I do not see the value of first filtering a number, then filetering something else.
My first idea, and yes it might ne a trickle slower is to have one textbox (cell) where you type the searchstring and like Google it filters as you type, let it trigger after three characters
Think about it.
Will require much less VBA and it can be modified in a simple way

So...Your answer to...

Are you wanting to filter..
Name then Last Name then Phone then ID having them all work in sequence

...knowing that telephone number or Id number can never be same?

The OP should really explain what he wants or explain the logic he expects; we've all looked and added our ideas, it's now time that a clear and concise answer is given. We cannot build on assumptions and and guessing

Here is one final attempt...
Column E is formatted as text...When you enter a new value in Column E the code will automatically format as text...
This allows for All | Individual or No textbox filters...

Please Login or Register  to view this content.

What really slows down the table is the column and row colouring conditional formatting.
I removed that and modified the Final, only one textbox and you type in what you're searching for.
You can type anything and it filters as you type

Post 46 solves...

well, not quite.

What you mean...It filters Col 2 or 3 or 4 or 5 as per requirement...What does it not do...
The least you can do is reference the file in Post 46 and give reasons for your statement so we can ascertain your actual requirement...

I suggest also adding a few non duplicates in Col's D & E so that you can actually see what the code does...

I hope all the efforts lead to something satisfying
Happy coding everyone

Originally Posted by Keebellah

I hope all the efforts lead to something satisfying
Happy coding everyone

Yeah, I'm also over it...

And still no feedback regarding Post 46...Have you even tried it...

I thought that saying "auto filter as you type for numbers with VBA" was pretty clear

Without manipulation this is not possible...Autofilter by itself is for text...
Hence the simplest solution is converting all Col E to text which will have all the Textboxes work in sync...Making use of one code snippet for all...

Thank you Sintek.

I did tried the solution on post #46. I tried each and every one and I combined them too. I'll keep using the one you gave in post #37 for now. It helps greatly. Thanks to every one, again.

The problem with making that column into text is that then I will have to enter each cell and press enter so that it is taken as text. Just setting it as text does not work either, the code does not "see" the text and when applying the filer it shows nothing. If I go into some cells and press enter then it will show filtered results. Do you know a VBA code that can forcefully convert the whole column from what it is now to text? And I mean real text as if I had gone inside the cell and pressed enter. You see, the actual spreadsheet has about 10K entries. My masochist level is very low.

Sintek, if I find a solution that works I will post it here.

that then I will have to enter each cell and press enter so that it is taken as text.

The code I supplied does this automatically...No extra work needed...

Just setting it as text does not work either, the code does not "see" the text and when applying the filer

Have you tried entering a value in Col E to see what I mean...
That is why i said it filters all or each individual column...

I hope @Sintek doesn't complain my intruding again, but you may combine anything in the Worksheet Change, it's time you try and follow some tutorials and try it out, THINK like the code it's nothing more, nothing less than executing one line of code at the time.
You tell is if this then do that else do something else.
I combined it for you and it works with Sintek's file and perfect solution

Please Login or Register  to view this content.

Oh nad if you are really happy with your file, try and avoid empty table rows !!! It's sloppy and user unfriendly

What is the purpose of code in Post 58...Just want to make sure I understand and test with correct or incorrect entries in Col D

@ Keebellah

I hope @Sintek doesn't complain my intruding again

Never...All part of the learning & sharing process...

I seriously doubt if the OP even realizes what het actually wants, the filtering I understand (except for the fact that empty dates when other fields are filled and the unnecessary and sloppy empty rows.)
The rest is probably entry verification but what the OP does not realize is that a worksheet change triggers with every character you type, suggest selection change, but then he will have to heed the fact that when filtering it will trigger a selection change event too

Yes, true...that's why I always make use of Userform Interface & Listboxes with Excel hidden...

True, I do too, but ideal working design is more that just helping out, but like experience tells us, you extend a finger ... the hand is next
Anyway, VBA is fun, you either love it or hate it. I've been 'playing around' with programming since 1983, from HPBasic to dBase later first macros in older Excel to VBA and still enjoy it

I only started dabbling with Excel in 2015 and fell in love with it immediately...This Forum has taught me so so much and learning everyday more & more...And Yes Happy Coding...

Like the saying goes (and very true) you're never too old to learn

Amen to that...

FYI, cross-posted: https://www.mrexcel.com/board/thread...umbers.1169911

Guys,

Thank you for helping me out. Please allow me some time to check the new replies (code). Unfortunately, I work at a public school and we are very busy (most of the time I even have to skip lunch) and when I go home I simply crash until next day. I have to force myself to stay up at night and go through this. Teaching all day and also helping with the technology for our students does take the whole work day and many hours after that. I am sometimes driving home and I have to Zoom (the app zoom) and try to help the student with their computer problem. This is not an excuse but it's just so you know why it takes me a while to check this out.

About the comment on "I seriously doubt if the OP even realizes what het actually wants" trust me that I know what we need. I'm very old and I too have been around computers since 1988. I am not familiar to VBA but I have done some programming in the past.

Also, about the comment "you extend a finger... the hand is next" I do not do that, and I hate that too. I am still after the same request as it was stated in post #1. I am still racing just one finger, the same finger.

If you are willing, I could give you a zoom link (no need to create an account on your part, I just need an email where to send the private and direct zoom link and you need to install the Zoom program in your computer, once installed don't login or anything, the link I will give you will take you directly to a private meeting with me) and I can show you "live" what is going on (the errors I get, what we need, and why we need it that way). You see, I am "boots on the ground" as they say. I think "programing" and computers should move around our needs instead of us molding our solutions around technology. Just saying.

Any how, I will try the last code given by Keebellah and I will let you know. Just give me some time to reply.

Rorya, about the cross-posting... not quite. I am very specific about the things I ask. On that other post, I was asking to adapt the code posted there to filter numbers. I did not ask for a different solution or for new code, but I can see how it seems the same from your perspective.

Yes, I also agree that I need to look into a tutorial for VBA... but I haven't had the time. I will do that in five weeks when we go off for the summer. Just to type these lines it has taken me 3 hours because I do it a minute here, a minute there, etc.

About to try to avoid empty rows... we need to add empty rows, we add them daily to leave them ready to receive data. The users who will enter the data will only do that, enter the data. We have some of our students helping input the data and they sometimes erase instead of add rows (it's a good hands-on-training and extra credit for them). Protecting the worksheet is not an option because then the Filter buttons do not work (a well known limitation of Excel 2010 - I've used VBA to lock the sheet but that does not work either).

Allow me sometime (at least 12 hours) and I will reply about the last code given.

Hi, Luis is it?
First let me excuse myself for my rude wording which were really meant to trigger a reaction and better information, okay the finger and the hand has been an issue sometimes)
I appreciate your thorough, honest explanation and clarification, well 'old' is a term I see as somebody with a back-pack carrying a lot of experience with him/her. I've filled mine for 72 years and there's still place for more
Once you've programmed (any language), then you'll notice it's all identical safe for syntax differences.
The way I see programming is like writing a book, you start with the first line and it all follows one after the other, the actions you want to execute are step-by-step instructions (literally) that tell the application what you want en not the other way around, the only thing involved is respect the syntax; programming lis limited to a user's imagination, inventivity and tow very important ingredients, time and perseverance (read patience) for troubleshooting.
A zoom link might come in handy some time but seen the time difference (me in Europe) and you in LA... lets take a raincheck

Let's get back to the file, the empty rows is a poor excuse, you do not need those for entering data, but then again time is your enemy because it would require coding for a userform.
Since your working with a listobject (Excel table) you could avoid that and add a button above the table reading with the caption 'Add Record' that adds a new empty row to the table where the user can enter his/her data.

I suggest you visit thespreadsheetguru site, one link I'm sharign is this one:
https://www.thespreadsheetguru.com/b...0with%20Tables
I's simple and clear for working with listobjects.

If you can explain what you want to achieve when filtering, is just looking for an entry or another need maybe we can go deeper into that.

Originally Posted by Luisftv

Rorya, about the cross-posting... not quite. I am very specific about the things I ask. On that other post, I was asking to adapt the code posted there to filter numbers. I did not ask for a different solution or for new code, but I can see how it seems the same from your perspective.

Because it is the same. Your question there is on exactly the same topic as the one here - how to autofilter numbers as you type.

The error you get once in a while during filtering is due to the fact that what you type is not present and the autofilter cannot crop with it.
I would suggest the use of the method I posted where I used the hidden column to take care of the filtering.
No need to convert any number cells to text or v.v. Just one textbox to start typing the search criteria should be enough, you just start typing an filtering on the fly.
Or you type a name or a number

This column cannot have anything other than numbers

Why...Oh Why...

is the one provided in post #37 (by Sintek),

This changes the data to text filters then changes back to numbers...Can only be done if the Table is Not Filtered already...So cannot work in sync with other filters...
only way to achieve sync filters is if all columns are Text...

Hence Post 46 is set up as such and with Keebelah changes allows for your other worksheet events to work...

Further to Post 76...Is this really gonna cause issues...
Untitled.png

ome array formulas in place to do some calculations (numbers are needed for that).

My suggestion is to fix that other code of your that does this...Always room for improvement...

Not in sync though...With other filters

True... for post #37 solution.

However, the solution you provided in post #46, when adjusted by Keebellah in post #59 and then used with the VBA code button to convert to text permanently the whole column (using the "text to column" built-in feature of Excel) does work and allow all the filter buttons to work with one another.

That's is some true "happy coding" if I say so myself.

"coding happy it is." "solution at hand I have."

Awesome...we have a winner ladies & gentleman...SOLVED....

And now wait for the [ SOLVED] button to be pressed.
And of course, my own final contribution

Keebellah,

I really like this v3 you made. It works flawlessly. I will incorporate it to our main file.

Thank you so much.

I just thought that if I remove all the column labels from the concatenated array formula and only leave the one for the ID then it does exactly what I need: filter by numbers. Wow! I know kung fu!! (Neo, from the Matrix) - Of course the beauty of it is the "all-inclusive" one search button. Beautiful!!!

I cleaned the array formula in row 9 from the extra unneeded code which did not interfered but still (it was a small typo). I also removed the macro from the module which is not needed at all. It was left there from one of the variants.

Have a blessed week and stay safe.

Great, use it to your needs and enjoy the code, it's not that difficult, just a little tricks and imagination will do it all
Happy coding and...
"IT" Always crosses your path ...