Steve_Brady
asked on
Code to create filtering box at the top of several columns in Excel
Hello,
This question is a follow-up to a thread found here:
https://www.experts-exchange.com/questions/28246564/Create-a-column-filter-cell-at-the-top-of-columns-in-Excel.html
I'm hoping someone can create a VBA code which will enable the placement of a row of "filtering" cells atop a range of data in an Excel spreadsheet. The functionality in this row of cells would be exactly the same as the search box in the drop-down menu when using basic filtering, as shown here (Fig. 1):
To illustrate, suppose you have a spreadsheet which contains the table shown in Fig. 2:
In this table, column headings are displayed in row 2 and the data is found in the range B4:H13. The objective is to have an additional row (row 3 in this case) in which search criteria can be inserted for any column.
For example, suppose you want to filter the table so that only individuals less than age 40 are displayed. Therefore, when that entry is made in search cell F3, all rows which do not meet that criterion are hidden (Fig. 3):
Next, suppose you want to decrease the number of displayed rows further by filtering for males. This would be done by entering the criterion in cell D3 (Fig. 4):
I realize that this type of filtering can be accomplished using advanced filtering. However, that process seems to require multiple steps each time you want to change filtering criteria. I'm hopeful that VBA code can be written so that nothing more is required than entering search criteria into a designated row.
Thanks
This question is a follow-up to a thread found here:
https://www.experts-exchange.com/questions/28246564/Create-a-column-filter-cell-at-the-top-of-columns-in-Excel.html
I'm hoping someone can create a VBA code which will enable the placement of a row of "filtering" cells atop a range of data in an Excel spreadsheet. The functionality in this row of cells would be exactly the same as the search box in the drop-down menu when using basic filtering, as shown here (Fig. 1):
To illustrate, suppose you have a spreadsheet which contains the table shown in Fig. 2:
In this table, column headings are displayed in row 2 and the data is found in the range B4:H13. The objective is to have an additional row (row 3 in this case) in which search criteria can be inserted for any column.
For example, suppose you want to filter the table so that only individuals less than age 40 are displayed. Therefore, when that entry is made in search cell F3, all rows which do not meet that criterion are hidden (Fig. 3):
Next, suppose you want to decrease the number of displayed rows further by filtering for males. This would be done by entering the criterion in cell D3 (Fig. 4):
I realize that this type of filtering can be accomplished using advanced filtering. However, that process seems to require multiple steps each time you want to change filtering criteria. I'm hopeful that VBA code can be written so that nothing more is required than entering search criteria into a designated row.
Thanks
Is it OK to have the filter criteria rows above the data? (the built-in filters won't work otherwise so everything would have to be hidden/unhidden in code)
ASKER
>>Is it OK to have the filter criteria rows above the data?That's fine assuming I correctly understand what you're describing. For example, in Fig. 2 above, the headings are in Row 2, the search criteria are intended to be entered for various columns in Row 3 (yellow), and the data begins in Row 4. Does that fall into your meaning of having the "filter criteria rows about the data"?
>>everything would have to be hidden/unhiddenCould you define "everything"? The three parts (headings, search criteria & data) all seem to be crucial but are you referring to any of those?
Thanks Rory
No - I mean that the criteria cells would have to be above the headers otherwise it's a case of reinventing the wheel and having the code hide each row as required.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a ton Rory! This is perfect and will help me a lot by saving numerous keystrokes and mouse clicks. The only thing that could possibly make it better is if it auto-adjusted in real time as you are typing (i.e. without having to press {Enter} to get the result). However, I've never seen anything in Excel that is dynamic like that. This is great though. Thanks again.
I can think of a couple of ways to do that but neither is ideal (keyboard hook, or activex textboxes). It might be possible to bodge something with Application.Onkey - I'll try and have a play tomorrow.
ASKER
LOL, the auto-adjust comment was made sort of tongue-in-cheek. However, because you're actually having a go at it, I'll close this thread and post that part of it as a new one. I will paste the follow-up link below once I've opened it. Thanks again Rory, for your time & assistance on my behalf—both on this topic and several others in the past. I really appreciate it.
You're welcome, Steve. :)
I won't have time today as it happens anyway - life getting in the way.
I won't have time today as it happens anyway - life getting in the way.
ASKER
Here's the link to the follow-up thread:
https://www.experts-exchange.com/questions/28251712/Real-time-filtering-in-Excel.html
https://www.experts-exchange.com/questions/28251712/Real-time-filtering-in-Excel.html
ASKER
Hey Rory, this code is so cool. It's saving me tons of time and aggravation so many thanks once again.
There is one thing that's happening which I don't understand and which is a bit of a problem but since this thread is already closed, I will be starting a new one in a few minutes. So just a heads up.
There is one thing that's happening which I don't understand and which is a bit of a problem but since this thread is already closed, I will be starting a new one in a few minutes. So just a heads up.
ASKER
I had visitors so my "few minutes" turned into a lot longer. :P
But the follow-up thread is posted now and is located here:
https://www.experts-exchange.com/questions/28252688/Problem-with-very-cool-filtering-VBA-code-for-Excel.html
But the follow-up thread is posted now and is located here:
https://www.experts-exchange.com/questions/28252688/Problem-with-very-cool-filtering-VBA-code-for-Excel.html