Karen from Chicago has an Excel worksheet with 200 columns. She wonders if there is a fast way to determine which columns have a filter applied and what filter is applied?
In this episode, I use some VBA from Excel MVP Roger Govier.
Copy this code or download a workbook from Which Excel Columns are Filtered?
Links in the video:
1) AutoFilter VBA properties from Autofilter VBA Operator Parameters
2) The UserVoice request with one vote: Excel for Windows (Desktop Application): Hot (7770 ideas) – Welcome to Excel’s Suggestion Box!
3) Buy the Excel Insights MVP Book: Excel Insights book by Excel MVPs
In this episode, I use some VBA from Excel MVP Roger Govier.
Copy this code or download a workbook from Which Excel Columns are Filtered?
Links in the video:
1) AutoFilter VBA properties from Autofilter VBA Operator Parameters
2) The UserVoice request with one vote: Excel for Windows (Desktop Application): Hot (7770 ideas) – Welcome to Excel’s Suggestion Box!
3) Buy the Excel Insights MVP Book: Excel Insights book by Excel MVPs
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2306.
Which Columns Are Filtered?
Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen and this question came from Karen in Chicago.
Karen has a worksheet with 200 columns.
The filter drop-downs are enabled and she wants a quick way to see which columns have a filter applied and what filter is applied to those columns.
Now Karen already knows to look for the funnel icon in the header.
Oh, but with hundreds of columns that can be time consuming. Hard to find.
Now this is not a good sign.
I went out to excel.uservoice.com to see if there's something there and there is. With only one vote.
One vote means that the Excel team is never going to do it.
If you don't get 20 votes, you have no hope, frankly. So that's a bad sign.
That means that Microsoft will never do this, so I went out to the MVP mailing list.
I said, Hey, you know this is an interesting problem.
Does anyone have a solution to this and that morning, Excel MVP Roger Govier says, yeah, hey, I wrote an article about this on.
Debra Dalgleish's site years ago.
It's based on some code and he kind of adjusted the code a little bit and I took Roger's code and using an article at YourSumBuddy.com - I'll put this link down in the YouTube description.
Found some extra code to round this out.
Roger basically is going to loop through all of the columns in the Sheet.AutoFilter and we're trying to figure out, based on some things.
So we have Filters dot item, this item he's looping, looping through those items and then .Criteria1, and .Criteria2, and then .Operator.
So we got these three things that we can investigate for each column to try and figure out if a filter is applied.
Alright and Roger realized that sometimes Criteria1 is going to be an array so check to see what the upper bound of the array is if that's NOT an error, then we know that we have to loop through those.
So we loop from 1 to item count and will get a list of all of the criteria that are used.
If it's not an array, then we know that there's just a single value for criteria.
It's interesting, Criteria2 is only used if you choose OR as the operator, and then down here the operator has a series of values.
This is where the YourSumBuddy.com code came in.
Really helpful to try and figure out these, so if the operator is set to 0 then it's a single item. 1 is And. 2 is Or.
3 is Top 10. Four is bottom 10. Top 10%. Bottom 10%.
(Got a typo there.) Seven is filter values.
8 is filtered by cell color. nine is filtered by font color.
10 is filtered by icon and this .Criteria1 index tells me which icon number to use or was selected, and then if it's 11 that's bad.
That means that they chose one of the dynamic filters and from there if they chose the dynamic filter then you want to go back to the value that was stored in .Criteria1 and it's going to be 1 of 34 codes. So 1 is today. 2 is yesterday.
3 is tomorrow. 4 is this week. 5 is last week.
I'm not going to read them all.
You can download the workbook - the link is down in the YouTube description to see all of these various codes.
Alright, so every time we have a column that is filtered, I appended to the end of a message, so message equals the old message plus which column were in and then the operator the second item if it's OR and then the type of filter and then a linefeed.
Alright if it happens that there's nothing filtered, then a report no columns filtered.
And then show that message. alright so we'll come back here.
You see that the in this data we have, I think, about 120 columns and you can see that there's some some filters applied, but it really is a pain to go through and catch which ones have the filter icon on. Which ones are dashes right?
So this seems like it's going to be better.
I'll press Alt F8.
And say they want to MessageTheFilterValues, click run alright and very quickly we get this a list.
AG1 is Andy Chris, Diana, Fred AN1 is greater than that date and it's a single item filter. Dynamic to this quarter filter.
Dynamic to year-to-date. AZ1 is using the OR for Andy or Barb.
BL1 is a single item Barb. BQ1 is a single item greater than 120.
BB1 is above average.
This is interesting. This is really interesting.
So here in BW1 someone chose the top 10 but it gets recorded as greater than or equal to 966.
That was wherever the top ten was at that point. DT1 someone used filter by icon and it's icon 5.
And then DW1 filter to color 255. Of course, being the code for red.
This is what Karen asked for, but I suspect that this is not useful, right?
Because you really want to go see those things right?
This was Rogers idea to improve. He said look, why don't we just record that information?
So the difference here between this sheet and the other sheet is we insert three rows.
Now the code doesn't check to make sure you inserted three rows, it will just create havoc if you didn't insert three rows.
So always make sure to insert three rows and will run the adapted code here that's called ShowFilterValues.
Click run right, very quick it's done.
And then here I can just press control right arrow to jump to the first column that's filtered and then control right arrow. And they all show up there.
Roger even put the item in red.
Very cool, very handy, very easy to use to see those items because you can just use control right arrow or CTRL Left arrow to jump to the next one.
I think this might be a little bit better although you have to remember to insert the three extra rows right?
So either way right again down in the YouTube description.
With Rogers permission I have his code with my modifications down there.
Feel free to download this.
I'm guessing is not going to be a popular thing to do, given that there's only one vote out on excel.uservoice.com, but I ran into Karen in Chicago, who presumably is not the person who voted excel.uservoice.com, so there's a few people out there who will find this useful.
And if you're one of those, thanks for finding this video now.
Hey, you know a shout out here to Roger for that code.
Roger actually wrote about advanced filters.
That was his chapter in the Excel Insights Book.
This comes out April 1st, 2020, ha, but we have a few copies here now for sale.
Click that "I" in the top right-hand corner.
And if you like what you see here, these weird Excel problems that I run into people who have these problems.
Please subscribe and ring that Bell so you can be notified when we have additional videos.
Feel free to post any questions or comments down in the comments below.
I thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Which Columns Are Filtered?
Hey, welcome back to MrExcel Netcast.
I'm Bill Jelen and this question came from Karen in Chicago.
Karen has a worksheet with 200 columns.
The filter drop-downs are enabled and she wants a quick way to see which columns have a filter applied and what filter is applied to those columns.
Now Karen already knows to look for the funnel icon in the header.
Oh, but with hundreds of columns that can be time consuming. Hard to find.
Now this is not a good sign.
I went out to excel.uservoice.com to see if there's something there and there is. With only one vote.
One vote means that the Excel team is never going to do it.
If you don't get 20 votes, you have no hope, frankly. So that's a bad sign.
That means that Microsoft will never do this, so I went out to the MVP mailing list.
I said, Hey, you know this is an interesting problem.
Does anyone have a solution to this and that morning, Excel MVP Roger Govier says, yeah, hey, I wrote an article about this on.
Debra Dalgleish's site years ago.
It's based on some code and he kind of adjusted the code a little bit and I took Roger's code and using an article at YourSumBuddy.com - I'll put this link down in the YouTube description.
Found some extra code to round this out.
Roger basically is going to loop through all of the columns in the Sheet.AutoFilter and we're trying to figure out, based on some things.
So we have Filters dot item, this item he's looping, looping through those items and then .Criteria1, and .Criteria2, and then .Operator.
So we got these three things that we can investigate for each column to try and figure out if a filter is applied.
Alright and Roger realized that sometimes Criteria1 is going to be an array so check to see what the upper bound of the array is if that's NOT an error, then we know that we have to loop through those.
So we loop from 1 to item count and will get a list of all of the criteria that are used.
If it's not an array, then we know that there's just a single value for criteria.
It's interesting, Criteria2 is only used if you choose OR as the operator, and then down here the operator has a series of values.
This is where the YourSumBuddy.com code came in.
Really helpful to try and figure out these, so if the operator is set to 0 then it's a single item. 1 is And. 2 is Or.
3 is Top 10. Four is bottom 10. Top 10%. Bottom 10%.
(Got a typo there.) Seven is filter values.
8 is filtered by cell color. nine is filtered by font color.
10 is filtered by icon and this .Criteria1 index tells me which icon number to use or was selected, and then if it's 11 that's bad.
That means that they chose one of the dynamic filters and from there if they chose the dynamic filter then you want to go back to the value that was stored in .Criteria1 and it's going to be 1 of 34 codes. So 1 is today. 2 is yesterday.
3 is tomorrow. 4 is this week. 5 is last week.
I'm not going to read them all.
You can download the workbook - the link is down in the YouTube description to see all of these various codes.
Alright, so every time we have a column that is filtered, I appended to the end of a message, so message equals the old message plus which column were in and then the operator the second item if it's OR and then the type of filter and then a linefeed.
Alright if it happens that there's nothing filtered, then a report no columns filtered.
And then show that message. alright so we'll come back here.
You see that the in this data we have, I think, about 120 columns and you can see that there's some some filters applied, but it really is a pain to go through and catch which ones have the filter icon on. Which ones are dashes right?
So this seems like it's going to be better.
I'll press Alt F8.
And say they want to MessageTheFilterValues, click run alright and very quickly we get this a list.
AG1 is Andy Chris, Diana, Fred AN1 is greater than that date and it's a single item filter. Dynamic to this quarter filter.
Dynamic to year-to-date. AZ1 is using the OR for Andy or Barb.
BL1 is a single item Barb. BQ1 is a single item greater than 120.
BB1 is above average.
This is interesting. This is really interesting.
So here in BW1 someone chose the top 10 but it gets recorded as greater than or equal to 966.
That was wherever the top ten was at that point. DT1 someone used filter by icon and it's icon 5.
And then DW1 filter to color 255. Of course, being the code for red.
This is what Karen asked for, but I suspect that this is not useful, right?
Because you really want to go see those things right?
This was Rogers idea to improve. He said look, why don't we just record that information?
So the difference here between this sheet and the other sheet is we insert three rows.
Now the code doesn't check to make sure you inserted three rows, it will just create havoc if you didn't insert three rows.
So always make sure to insert three rows and will run the adapted code here that's called ShowFilterValues.
Click run right, very quick it's done.
And then here I can just press control right arrow to jump to the first column that's filtered and then control right arrow. And they all show up there.
Roger even put the item in red.
Very cool, very handy, very easy to use to see those items because you can just use control right arrow or CTRL Left arrow to jump to the next one.
I think this might be a little bit better although you have to remember to insert the three extra rows right?
So either way right again down in the YouTube description.
With Rogers permission I have his code with my modifications down there.
Feel free to download this.
I'm guessing is not going to be a popular thing to do, given that there's only one vote out on excel.uservoice.com, but I ran into Karen in Chicago, who presumably is not the person who voted excel.uservoice.com, so there's a few people out there who will find this useful.
And if you're one of those, thanks for finding this video now.
Hey, you know a shout out here to Roger for that code.
Roger actually wrote about advanced filters.
That was his chapter in the Excel Insights Book.
This comes out April 1st, 2020, ha, but we have a few copies here now for sale.
Click that "I" in the top right-hand corner.
And if you like what you see here, these weird Excel problems that I run into people who have these problems.
Please subscribe and ring that Bell so you can be notified when we have additional videos.
Feel free to post any questions or comments down in the comments below.
I thank you for stopping by and we'll see you next time for another netcast from MrExcel.
