+ Reply to Thread
Results 1 to 88 of 88

Auto Filter as you type for numbers with VBA

  1. #1
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    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.
    Last edited by Luisftv; 04-29-2021 at 10:40 PM.

  2. #2
    Valued Forum Contributor dotchiejack's Avatar
    Join Date
    05-21-2015
    Location
    Antwerp,Belgium
    MS-Off Ver
    2016
    Posts
    507

    Re: Auto Filter as you type for numbers with VBA

    Please Login or Register  to view this content.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    Using your own methid:

    Please Login or Register  to view this content.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

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

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Auto Filter as you type for numbers with VBA

    Pl see file. Code is working.
    Code
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    Thank you everyone. All the solutions provided work. I appreciate it.

    Have a blessed day.

  8. #8
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    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.
    Last edited by Luisftv; 04-30-2021 at 10:13 PM.

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    @kvsrinivasamurthy code does that

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Auto Filter as you type for numbers with VBA

    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.

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    I copied your code and it does what the OP wants

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    Here is the code as provided by sintek
    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Auto Filter as you type for numbers with VBA

    Quote Originally Posted by Keebellah View Post
    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.

  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

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

  15. #15
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    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.
    Last edited by Luisftv; 05-01-2021 at 12:06 PM.

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

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

  17. #17
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Auto Filter as you type for numbers with VBA

    see #12
    With ... End With is not necessary.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Messages have been translated from Dutch to English by means of google translate.

  18. #18
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    I hope I make more sense now.
    Post 12 solved...As well as VenA's simplification...

  19. #19
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    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.
    Last edited by Luisftv; 05-01-2021 at 12:53 PM.

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    Doesn't happen with my file, just tested it

  21. #21
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Auto Filter as you type for numbers with VBA

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

  22. #22
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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

  23. #23
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    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.
    Last edited by Luisftv; 05-01-2021 at 09:39 PM.

  24. #24
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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...
    Last edited by sintek; 05-02-2021 at 02:07 AM.

  25. #25
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    YES, I had overlooked that. I honestly thought I had already set the column to text. As they say "the evil is in the details".

    Thanks.

  26. #26
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    Glad you got it sorted...

  27. #27
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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.

  28. #28
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    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.
    Last edited by Luisftv; 05-02-2021 at 03:22 PM.

  29. #29
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    Keebellah,

    Do you have the last code you provided (using the hidden column C with concatenated columns) but in a way that it will not remove the last character entered in the filter button if the text is not found? In other words, if the searched text is not found, leave the search string intact in the filter button.

    Thank you.

  30. #30
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Auto Filter as you type for numbers with VBA

    Give it a try with advanced filter.

    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    Vraag en antwoord,

    The code you provided (using the helper columns and the formula in AA2) works as it is needed in every respect except that when you clear the filter button then Excel takes a long time to bring back the table to the previous state (unfiltered). I deleted most records in my table and I left about 200 rows and it still took a full minute to clear the filter (Excel even became "unresponsive" while clearing the button search string. Other than that, it works great, but the speed of clearing the filter is a killer. What can be done to make it faster?

  32. #32
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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.

  33. #33
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

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

  34. #34
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Auto Filter as you type for numbers with VBA

    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
    Last edited by Vraag en antwoord; 05-02-2021 at 05:41 PM.

  35. #35
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Auto Filter as you type for numbers with VBA

    Another possibility...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  36. #36
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    I just finished (it took me a few days) mimicking the actual file (the one sheet where this is happening) that we are using so that I can upload it here (the original workbook has some sheets that are password protected and I don't have the password and extracting one sheet is not allowed). The file has other code and conditional formatting which does not interfere with what I'm trying to accomplish but I added it to this file because the original has it and some of the code could/may interfere with the solutions that are being provided.

    Please see attached two files. The column in question is E, the fifth one, label as "ID".

    File one with the solution provided by kvsrinivasamurthy works (and fast enough regardless of how many rows) but adds zero to all empty cells where the filter is applied. Can this solution be modified not to add the zeros and cells could be set to numbers? If possible.

    File two with the solution provided by Vraag en antwoord works in every respect but it becomes too slow when clearing the filter. The column with numbers is formatted to "numbers". This is ideal. If the speed could be improved, then it's a winner.

    Both files are exactly the same (with some dummy data and about 400+ rows) except for the solutions applied.

    Vraag en antwoord, see how slow it is to empty the filter criteria. We are talking about file number two. The code works in every respect but it clears the filter way too slow. By the way, the last file where you added the clear filter button has also become slow.

    The PREFERRED solution is one that
    - does not hide any rows, empty or not (once the filter is cleared, it should go back to how it was)
    - the range (the column containig the data) could be set to number (number is the ideal) or general. If set to text, I will have to convert all the thousands of rows from numbers to text and for some reason Excel is only letting me do it when entering the cell one by one and pressing enter. This would take forever. It must be some of the conditional formatting that is making it so
    - does not add zero or anything
    - is fast (like the buttons for text already in place)
    - it should find/match the search criteria in any part of the cell
    - the solution should allow modification so that later can be easy to apply to a new filter button. That way, all that will be needed is to copy/paste some part of the code and to modify/add the button name and the "field" position.

    In the meantime, I will start applying the last few solutions provided and see if they work.

  37. #37
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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.
    Attached Files Attached Files
    Last edited by sintek; 05-03-2021 at 01:58 AM.

  38. #38
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    I get this when you run the code from whiting the console (view code), using the Snippets taken from jindon:

    Run-time error '91':
    Object variable or With block variable not set.
    I also get the same error when first using all the other filter buttons and in the end using the column E filter, at which point after clicking the OK button to make the error message disapear it starts filterring, but the other filters then do not work. However, if I clear the filter in any of the other search buttons and then enter again a search criteria in one of them (but not in column E) then all other filter start working but the one in column E will not... and so on in a loop.

    Another peculiar thing is that if when I open the file I start filtering with any of the other buttons (no the one in E) they work, and if I then use the one in column E then everything disapears. Clearing the filter in E and entering it again makes it work, but again, all others get disabled.

    See attached file.

    I will try the VenA code next and let you know.
    Attached Files Attached Files

  39. #39
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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...
    Last edited by sintek; 05-03-2021 at 02:58 AM.

  40. #40
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

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

  41. #41
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    As of now (not counting column E), all the filter buttons work independent of each other, in sequence or not. The code for each button is independent (self contained) of all other buttons' code. If I filter by Name and then I also filter by Phone (not in sequence) it works as well as if I filter in sequence.

    So my initial requirement is still the same: to filter as I type the column that has only numbers. What I didn't realize is that the solution would cripple the existing code (of the other buttons). I thought the solution would also be independent, self contain, as the others are.

    That's why now I managed to provide a full replica of the actual table where I need the code to work.

    Ps. Using the code by VenA and adding the extra lines make it fast but renders the same result as when using the snippets taken from jindon (it goes on a loop of enable-disable-enable etc., unfortunately. See attached file.
    Attached Files Attached Files

  42. #42
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    Ditto !!!

    It's my fault. It took me a few days to replicate the actual table with all other vba and conditional formatting. At first, I did a basic table thinking the solution would be self-contained for only the column with numbers and it would not affect anything else.

    I apologize.

  43. #43
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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

  44. #44
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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?

  45. #45
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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

  46. #46
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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.
    Attached Files Attached Files
    Last edited by sintek; 05-03-2021 at 04:32 AM.

  47. #47
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    Very well. Let's go to the beginning as requested.

    The attached file has no code for column E which contains only numbers (the ID column).

    The existing code works for the Name, Lat Name, Phone, and eMail columns. Those columns' property are set to General which the VBA code handles as alphanumeric. Unfortunately, this existing code can't handle numeric data.

    The "Date" and "Telephone" columns although they have numbers are being treated as "text / alphanumeric" because of the ( ) parenthesis, hyphen and forward slash.

    So...

    What I need is to modify the code I have in place (or any code like what was provided in this thread) to be able to handle the numeric data (when it is only numbers and the cell's property is either General or Number).

    The approaches adapting code provided by "jindon" and "VenA" were almost there, except that it went on a loop of working and not working and interrupting the other buttons' code functionality.

    The ID column will only have numbers, always. No alphanumeric data will ever be in that column.

    Again, the code should:
    - not hide any rows, when empty or not (once the filter is cleared, it should go back to how it was)
    - work with column E set to general or number
    - does not add zero or anything to column E after applying or clearing the filter
    - work fast (like the code that handles alphanumeric columns)
    - it should find/match the search criteria in any part of the cell - math the string where ever in the cell is found.
    - the solution preferably should be self contained: the code that will work with the button for column E will not interfere or be connected/compiled/linked with the other buttons/columns. Pretend that only the ID column exist.

    Again, as originally requested: filter as you type numeric data (in a column)
    Attached Files Attached Files

  48. #48
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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

  49. #49
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    Post 46 solves...

  50. #50
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    "Post 46 solves..." well, not quite.

    Removing the conditional formatting does not help. All pre-existing code was working fine with that. It is only when trying to filter by numbers that the code either add a zero, a tilde, is too slow or breaks the functionality of the other buttons.

    There are three solutions provided that can do for the moment: the one that adds a zero to empty rows (thank you kvsrinivasamurthy), the one where it gets very slow (thanks Vraag en antwoord), and the one where I either only sort by the ID column or by the other columns but not in conjunction (adapted by sintek from jindon code). For now I will use either the first one or the third one while the correct solution comes up.

    I hope some one can see the "little" detail that can fix either one of the three solutions that are partially working, or another one. Please use the file provided here, which is the same as the one provided in my last post.

    Tomorrow I will upload a sample of the three partial solutions so you can see that the conditional formatting is not slowing down the code.

    Again, thank you so much for you help.
    Attached Files Attached Files

  51. #51
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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...
    Last edited by sintek; 05-03-2021 at 06:00 AM.

  52. #52
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

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

  53. #53
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    Quote Originally Posted by Keebellah View Post
    I hope all the efforts lead to something satisfying
    Happy coding everyone
    Yeah, I'm also over it...

  54. #54
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    For now, I will keep using the solution provided by SINTEK in post #37 (Sintek adapted snippets taken from jindon). It works, as long as I don't filter by the other buttons. It's either filter by text or filter by numbers but not both. I have to remember to clear the filter first before switching from one to the other. I still get the "Run-time error '91': Object variable or With block variable not set" every now and then (specially is you run the code in the code editor)... but it works.

    The funny thing is that the auto filters stablished in the table by Excel (when making the Auto Table) do work. Except that you must keep clicking on the pull down arrow and is not "as you type".

    Anyhow, it's a partial solution but it will do for now.

    The attached file shows the solution in action. If you get any error, just close the file, open it again and start filtering by column E. Notice that it is not slow at all and the conditional formatting is still in place.

    If any one ever finds a permanent solution, when testing the code "pretend" that the other VBA filter buttons are not there. The solution for filtering numbers in column E must work by itself (only for column E) and not interfere with the other VBA code already in the file, including the conditional formatting. It should not crash either when switching from filter button to filter button (which is the case now with the chosen "partial" solution).

    Thanks every one.

    ps. I'm surprise how confusing my request has been. I thought that saying "auto filter as you type for numbers with VBA" was pretty clear and direct but lately the impression is that I ask to "also link the code to any other filtering code already in place" or to "take into account other columns." Weird.

    I simply need to filter the numeric data in column E using VBA with a button so that I can "filter as I type" while that column's property (column E) is set to either General or Number - it's that simply (the request, I mean).

  55. #55
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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...
    Last edited by sintek; 05-04-2021 at 03:05 AM.

  56. #56
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    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.
    Last edited by Luisftv; 05-04-2021 at 05:45 PM.

  57. #57
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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...

  58. #58
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    Sintek,

    You are correct, the solution you provided in post #46 DOES WORK as you say and it works perfectly... but only if I remove another part of the sheet's code which we cannot afford to remove. The code crashes and it won't work at all, all of it. That solution in post 46 is beautiful and pragmatic. I've been trying to make it work but I'm not that proficient yet in this VBA.

    Here is the code already in place prior to this thread which does not allow me to use that excellent solution in post #46:

    Please Login or Register  to view this content.
    Of course, this code has nothing to do with the column E where the numbers are, but there can only be one "Worksheet_Change" and I can't merge both routines into one. Also, the "Function_USPhone" does not seem to crash it but is working in conjunction with the "Worksheet_Change" so I included that part just in case. Yes, I already tried renaming one or the other but I am doing something very wrong because it won't work. Maybe I'm too exhausted of looking at this issue. I don't know.

    In the file you provided as a solution, you removed the above code altogether so it works flawlessly... but we cannot afford to remove it unfortunately.

    Perhaps you can see a way to fix that incompatibility, to merge them, rename the sub, or something?

    That's why I was asking for a "self contained" solution.

    Thank you so much for not giving up on this. The partial working solution (also provided by you) that we are using right now is already making a huge difference.

  59. #59
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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.

  60. #60
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

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

  61. #61
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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...
    Last edited by sintek; 05-05-2021 at 02:49 AM.

  62. #62
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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

  63. #63
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

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

  64. #64
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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

  65. #65
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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...

  66. #66
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

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

  67. #67
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    Amen to that...

  68. #68
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Auto Filter as you type for numbers with VBA

    Rory

  69. #69
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    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.
    Last edited by Luisftv; 05-05-2021 at 03:10 PM.

  70. #70
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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.

  71. #71
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Auto Filter as you type for numbers with VBA

    Quote Originally Posted by Luisftv View Post
    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.

  72. #72
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    Keebellah,

    What I want to achieve is simply to "look for an entry" as you said it, and to display the whole row(s) that match that entry in that particular column and without changing the column cell's properties or adding an apostrophe or any thing else (temporarily is fine, like in the solution that Sintek provided which we are using right now - but the code crashes every now and then).

    I am planning to implement the form idea during summer.

    I will study the info in that link you provided. Thanks.

    Raincheck it is Sir.

    Programming is all the same as you said, but the syntax is where the issue is.

    In another thread some one provided the VBA code to use "Text to Column" to convert all the cells in the column to true text (like this, some of the solutions in this thread are doable). I am thinking of implementing a button with that code to convert "at will" the column's content to text all the time and then simply use the code that handles text and be done with this. Excel 2010 does not have LANBDA so I cannot write a routine that can do exactly what I need. Perhaps next year or so we will upgrade to Excel 2021.

    I'll check the last code in a few hours when I get off from work.

    By the way, no offence taken. It's all a learning experience.

  73. #73
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    rorya.

    Same topic? Ok. Specific request though, but I see your point.

  74. #74
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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

  75. #75
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    I tried Keebellah code provided in post #59 and all the filter buttons work except for the number (column E) one. See attached file. But...

    If I convert column E data to text using the "Column to Text" option in Excel, then Keebellah code in post #59 works BUT an apostrophe is added to the numbers. This column cannot have anything other than numbers. However, in another post there was a solution given to use the "column to text" using a VBA button which does work and it does not add the apostrophe. Now, like this we can use it and it can be considered an actual solution.

    The other solution which I still prefer (because there is no converting anything) is the one provided in post #37 (by Sintek), even if it crashes every now and then, and it has to be used by itself (it does not work alongside the other filter buttons in place). Let's not forget the solution (post #6 by kvsrinivasamurthy) that adds zero to the empty rows, that one works too (and it does not involve converting to text either), but the zero...

    I will leave this thread unsolved for a few more days so that I can prepare final example files showing the solutions found. I like doing that for the benefit of clarification and to make it easier to share the answers.

    Thank you.

  76. #76
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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...
    Last edited by sintek; 05-06-2021 at 05:36 AM.

  77. #77
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    Universal rules of life:

    1. Right understanding (knowledge is first, always)
    2. Un-attachment (letting go, not being entangle - this does not mean not to feel, but to feel without attachment, without selfishness)
    3. Sharing (there is no progress in anything whiteout sharing)

    Everything else comes by itself.

    I will post the solutions in this post soon.

  78. #78
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    As to why it cannot have anything else other than numbers... another sheet is linked to that column and in that other sheet there are some array formulas in place to do some calculations (numbers are needed for that).

    Don't worry. I am satisfied with the available solutions so far.

  79. #79
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

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

  80. #80
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    I cannot open the Attachment 731329. Ahhh... I can see it now.

    When you see it on the worksheet it looks as if only numbers are there. Go inside a cell and in the formula bar you will see the apostrophe added to the beginning of the number. The array formulas in another sheet crash when seeing that.

  81. #81
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    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...

  82. #82
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    Using your solution in post #37 and the one given in post #59 (when used with the VBA code to convert to text using the "text to column") do work without having to do any adjustments elsewhere.

  83. #83
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

    Not in sync though...With other filters

  84. #84
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    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."
    Last edited by Luisftv; 05-06-2021 at 06:16 AM.

  85. #85
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Auto Filter as you type for numbers with VBA

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

  86. #86
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA


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

  87. #87
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: Auto Filter as you type for numbers with VBA

    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.
    Last edited by Luisftv; 05-07-2021 at 02:07 AM.

  88. #88
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto Filter as you type for numbers with VBA

    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 ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Can i auto filter the numbers when i type? numbers
    By amrhosny in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2019, 12:14 PM
  2. Using Array in Auto Filter VBA -Type 13 error - more than 65536 records
    By kd7 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2017, 04:53 PM
  3. VBA - Auto Filter and make new sheets based on the filter values- Loop
    By Paul Cherian in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2017, 06:41 AM
  4. Replies: 1
    Last Post: 12-12-2014, 06:46 AM
  5. [SOLVED] Filter Table as i type in textbox1 but when erasing the text filter should be unfilter
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-17-2014, 09:08 AM
  6. Delete Rows hidden by either Auto Filter and Advanced Filter
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2013, 10:29 AM
  7. Auto Filter, Wilcards and Numbers
    By Karlos in forum Excel General
    Replies: 1
    Last Post: 05-16-2006, 11:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1