Shortcut for the "search" option in filters

You misunderstand. I don't want to search Excel for the filter option. I want to search WITHIN the filter option.

Darn. No VBA code or anything?

While working on a similar topic, I found out that MS did not provide programming access to the window shown. Probably the use of SendKeys remains, although I prefer the more difficult but sure way with keybd_event (Win API).
I present the full module provided by Kevin Wilson. I have disabled anything unrelated to the current task.
You must assign the Ctrl + q keyboard shortcut to the FindInAutoFilter procedure. Any other can be assigned as long as the appropriate code snippet is modified.

Please Login or Register  to view this content.

Artik

Thanks Artik! But I'm getting a compile error, code needs to be updated for 64-bit systems.

Replace the previous fragment regarding the declaration of the Win API function with the following:

Please Login or Register  to view this content.

It should work in both bit versions of Office.

Artik

Hi there,

This probably isn't what you're looking for, but if the Advanced Filter method does what you need you could try the following approach.

Cells A2:A101 contain the letters A to Z in random order.
Cells C1:E1 are the defined name range "ptrFilterValues" and (at present) contain the values Q, J and Y.

The following code will filter column A to show only those cells whose values correspond to the values in Cells C1:E1:

Please Login or Register  to view this content.

Hope this might be of some interest/help.

Regards,

Greg M

Hi again,

If you want an AutoFilter "lookalike" which displays a dropdown list of filter values, you could try the following approach which uses a defined named range "jotter" column to prepare the dropdown list:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

Greg, I really like the first bit of code. It works great on your sheet and is exactly the kind of thing that would make my life easier! But I'm having trouble applying it to my sheet. Excel is throwing compile errors and error 400. I'm a VBA newbie (use it a lot but couldn't write anything beyond basics) so I'm probably goofing up something simple.

Here's the file I'm working with. It contains a list of over 1000 clients (obviously I've revised this version of the sheet) that I need to enter data for and I'd like to simplify searching and filtering for clients on the list.

Originally Posted by jeffreybrown

Hi cpFAL96,

While it appears Greg is away right now, I'll throw a few things in he may ask.

1) What does the code debug too
2) Can you provide a sample workbook of what you've tried

Thanks Jeffrey. It's been throwing the 400 error with no description. When I debug there's no issue, but when I try to run the code the error pops up.

Originally Posted by jeffreybrown

The error throws because you have not defined a name range called >> ptrFilterValues

Open the VBA code and step thru the Sub FilterColumn by placing your cursor in the Sub and then pressing F8

I'm not all that sure from the spreadsheet what you are trying to filter

Oh duh, of course. But it's still not quite working. See attached. It's trying to filter but not leaving the filtered rows visible, if that makes sense.

Hi again,

From jeffrey:

I'm not all that sure from the spreadsheet what you are trying to filter

I'm afraid that I'm in the same boat - I can't really figure out what you're trying to filter.


Just for information, the code you're using to show/hide the various groups of columns can be greatly simplified. In the attached version of your workbook, the SAME "ShowSelectedName" routine is called by each of the 18 "Name" buttons on the worksheet.

The following code will replace all of your "S1" to "S20" routines:

Please Login or Register  to view this content.

If you can give me a bit more information about how you want to filter the worksheet rows I'll see what I can do to help.

Regards,

Greg M

Thanks Greg! I wrote all those "name" codes myself so you can tell exactly what an amateur I am, lol.

I'm trying to filter by client name. Essentially what I want to be able to do is type a portion of the client's name in the cell (A10) and have it filter out all clients that have that string of text in their name.

Hi Greg, sorry for not replying, I've had a super busy week.

I had the chance today to look at your code and it worked great for your sheet, unfortunately when I applied it to mine, it's not working. The only difference in my sheet - as far as I can tell - is that I have 1300+ clients listed. I updated the "i_MAX_NO_OF_CLIENTS" value to 1500 but otherwise I just copied and pasted your code into the appropriate modules. I'll post my Excel sheet shortly.

Side note but I also did notice the filter only works if I type the first characters of the name. It won't search within a string of text. (Like if I had "ABC Car Wash" I couldn't search for "car" and have it show.) Not the end of the world, this is already great, but do you know if there's a way to have it do this?

Sorry, my file is too big to upload.

Hi again,

Did you incorporate the "txtFilterValue_Change" routine into the VBA CodeModule for the "ClientStats" worksheet? It won't work if it is incorporated into a standard VBA CodeModule.

(Like if I had "ABC Car Wash" I couldn't search for "car" and have it show

To get the filter to work the way you describe, just replace the highlighted line in the following code extract:

Please Login or Register  to view this content.

with this one:

Please Login or Register  to view this content.

NOTE FOR EXPERTS:

If you want to see one of Excel's "quirks", just omit the "VBA." qualifier from the above statement - it seems that special tactics must be adopted when using the Instr function with named arguments.



Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Hi again,

Many thanks for your feedback.

You're welcome - glad I was able to help.

Regards,

Greg M