Mark G has an AutoFilter outside the print range and wants to show what is selected in the AutoFilter inside the print range. Excel 2013 Table Slicers could do this, but would take up a lot of space. Episode 1827 shows the VBA Macro used to return items selected from the AutoFilter to cells in Excel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1827.
Read Selected AutoFilter values.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, a great question sent in by Mark G, originally posted this Google plus.
He has a column with the auto filter outside the print range.
He's using show only certain rows on report.
He wants to get the selected items from that auto filter column, back into the report.
Is there a formula or something like that?
Well, I don't think there's a formula.
Well hang on, maybe there's a formula.
You could use equal subtotal or equal aggregate to check and see if the count of each individual cell is one, in other words is it visible.
Nevermind, I'm just going to go straight to VBA, maybe Mike Girvin could do that.
But let's go straight to VBA.
So, as I started to think about this, there's lots of different ways to auto filter, data and auto filter has improved over the years.
It's just called filter now, but in VBA, it's called Auto filter.
So, I'm going to keep doing that you know, it would be bad if you went in and select certain dates because they have all of these items that were added and so if you happen to be watching this video on YouTube because you search for this and you have a date column or if you used some sort of a custom value column like, between or something like that.
You're going to have a different situation.
I'm assuming, we just have some text and we're going in and selecting one or two or more items from the text and want to know what's selected and report that up here and the report that's what I'm assuming.
So, when I started to investigate this first thing.
I wanted to do is, I want to see how the macro recorder, records that action.
So, turn on the macro recorder and say how to filter.
We turn on the filters, go to column G over here and just select one, two, three items, click [ ok ].
All right! Stop recording.
Let's go look at that code, [ alt F8 ], how to filter, edit.
All right! So, they do selection dot auto filter and then they're using field six, so even though column G is the seventh column.
It's the sixth column in the data and then criteria one, they're passing an array to criteria one, operator is Excel, filter values.
All right! So, I know a little bit about using auto filter, there's always a criteria one often there's an operator, but not always and sometimes there's a criteria two.
So, we're kind of lucky here in this case where we're selecting text items.
We won't have to worry about criteria one, to see what's in there.
Although, it's an array, it's in there.
I did go out to Excel help, on the auto filter where they had this code, and I thought it was interesting code, useful code because it showed that they are checking to see if the worksheet auto filter dot filters dot item and then item 6 if the dot On, is true that tells us if anything is selected in that filter.
They also looked at dot criteria1 and check to see if dot operator, before they look to the operator and criteria2.
So, if you have one of those other situations where it's days or a custom filter, you're going to have to possibly use those but in my case, the code that we used was I think simpler.
Although, as we, as time went on, that's got more and more complex.
Let's ignore these cases right here for now and just deal with the start that assumes, that the auto filters turned on and something is selected from column 6 .
So, we start out Dim WS as a worksheet, Dim filter array as an array variable.
So, we don't know how many items are going to be in there but we specify just a blank set of parentheses there, that way it will expand as needed and then set WS equal to the active sheet and then use WS dot auto filter dot filter 6.
That's the sixth column, even though it's the seven column of the spreadsheet.
It's a six column in the auto filter and used dot criteria1, remember that's how it got recorded down here, dot criteria1 was equal to an array.
So, we're taking whatever is in criteria1 and assigning it to this filter array variable.
Now, the problem is we don't know how many items are in the filter array So, we use this cool function, I guess UBound stands for upper bound, in this case.
If I selected three items Ubound will be equal to 3.
All right! So, we're going to run this code.
Let's make sure that we have a few items selected.
And I'm going to stop it right here, this caused an error the first time that I ran it.
Because each of those items are in the filter array with an equal sign, see that equal "hurricane" and when you write that into the spreadsheet.
It thinks that there's a named range called hurricane, and you get you know, reference error or something like that.
So, because of that I had to use the mid of my texts starting a position 2, for a length of how many that gets rid of the leading equal sign.
I could have done that in all one statement of course, using the mid right there but I want to show you that interim result.
Go ahead and let this finish running Hurricane, rainy and windy and then if we change to something else.
Click [ OK ], click update titles, it will run there.
Okay! A couple of things, we had to handle the case where there was nothing selected.
So, it's just left in its default state and you'll see in the code.
I had to check if not, WS dot auto filter dot filters dot six dot On.
So, that's a true false thing, if that's not true, and we just write all.
Also, the chance that we have not turned the auto filters on at all.
So, if WS dot auto filter is nothing, that's an interesting statement.
I don't think I've used that in the podcast before.
That's a word say, here's an object variable if this has not yet been created.
If it doesn't exist yet, the words for that are, is nothing and we write, I actually just let's write, "no filter apply", filters not turned on and instead of all done here, we'll write (typing) selected.
Alright! So, a couple of tests turn the filters off, update titles, filters not turned on, turn the filter on, but don't select anything from item 6, everything selected and then the other one to be aware of let's turn on Sunny and Raining and Hurricane, click [ OK ], what a weird combination.
And click update titles, it shows us that but then if later on, we would filter another column.
Take the zero out of head one.
Now, Sunny is no longer here.
It's not even in the list anymore, but it's still secretly selected behind the scenes.
I know that because when I run the macro, they continue to show sunny as one of the items.
So, just be aware of that.
The other thing that was frustrating to me as I said hey this update titles would be really good, if we could just run this every time, I chose from the filter, but there does not seem to be a worksheet, event macro.
Selection change, change none of these are getting fired.
When we just change something from the Auto filter drop-down, so worksheet changed there.
If I come in here and type 1, it runs the macro, but if all I do is open the filter drop-down and change what's selected.
That does not fire that, so that's why I had it add the shape here, right click [ assign macro ].
I had to manually assign that macro to the...
Hey! I want to thank Mark for posting a question, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1827.
Read Selected AutoFilter values.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, a great question sent in by Mark G, originally posted this Google plus.
He has a column with the auto filter outside the print range.
He's using show only certain rows on report.
He wants to get the selected items from that auto filter column, back into the report.
Is there a formula or something like that?
Well, I don't think there's a formula.
Well hang on, maybe there's a formula.
You could use equal subtotal or equal aggregate to check and see if the count of each individual cell is one, in other words is it visible.
Nevermind, I'm just going to go straight to VBA, maybe Mike Girvin could do that.
But let's go straight to VBA.
So, as I started to think about this, there's lots of different ways to auto filter, data and auto filter has improved over the years.
It's just called filter now, but in VBA, it's called Auto filter.
So, I'm going to keep doing that you know, it would be bad if you went in and select certain dates because they have all of these items that were added and so if you happen to be watching this video on YouTube because you search for this and you have a date column or if you used some sort of a custom value column like, between or something like that.
You're going to have a different situation.
I'm assuming, we just have some text and we're going in and selecting one or two or more items from the text and want to know what's selected and report that up here and the report that's what I'm assuming.
So, when I started to investigate this first thing.
I wanted to do is, I want to see how the macro recorder, records that action.
So, turn on the macro recorder and say how to filter.
We turn on the filters, go to column G over here and just select one, two, three items, click [ ok ].
All right! Stop recording.
Let's go look at that code, [ alt F8 ], how to filter, edit.
All right! So, they do selection dot auto filter and then they're using field six, so even though column G is the seventh column.
It's the sixth column in the data and then criteria one, they're passing an array to criteria one, operator is Excel, filter values.
All right! So, I know a little bit about using auto filter, there's always a criteria one often there's an operator, but not always and sometimes there's a criteria two.
So, we're kind of lucky here in this case where we're selecting text items.
We won't have to worry about criteria one, to see what's in there.
Although, it's an array, it's in there.
I did go out to Excel help, on the auto filter where they had this code, and I thought it was interesting code, useful code because it showed that they are checking to see if the worksheet auto filter dot filters dot item and then item 6 if the dot On, is true that tells us if anything is selected in that filter.
They also looked at dot criteria1 and check to see if dot operator, before they look to the operator and criteria2.
So, if you have one of those other situations where it's days or a custom filter, you're going to have to possibly use those but in my case, the code that we used was I think simpler.
Although, as we, as time went on, that's got more and more complex.
Let's ignore these cases right here for now and just deal with the start that assumes, that the auto filters turned on and something is selected from column 6 .
So, we start out Dim WS as a worksheet, Dim filter array as an array variable.
So, we don't know how many items are going to be in there but we specify just a blank set of parentheses there, that way it will expand as needed and then set WS equal to the active sheet and then use WS dot auto filter dot filter 6.
That's the sixth column, even though it's the seven column of the spreadsheet.
It's a six column in the auto filter and used dot criteria1, remember that's how it got recorded down here, dot criteria1 was equal to an array.
So, we're taking whatever is in criteria1 and assigning it to this filter array variable.
Now, the problem is we don't know how many items are in the filter array So, we use this cool function, I guess UBound stands for upper bound, in this case.
If I selected three items Ubound will be equal to 3.
All right! So, we're going to run this code.
Let's make sure that we have a few items selected.
And I'm going to stop it right here, this caused an error the first time that I ran it.
Because each of those items are in the filter array with an equal sign, see that equal "hurricane" and when you write that into the spreadsheet.
It thinks that there's a named range called hurricane, and you get you know, reference error or something like that.
So, because of that I had to use the mid of my texts starting a position 2, for a length of how many that gets rid of the leading equal sign.
I could have done that in all one statement of course, using the mid right there but I want to show you that interim result.
Go ahead and let this finish running Hurricane, rainy and windy and then if we change to something else.
Click [ OK ], click update titles, it will run there.
Okay! A couple of things, we had to handle the case where there was nothing selected.
So, it's just left in its default state and you'll see in the code.
I had to check if not, WS dot auto filter dot filters dot six dot On.
So, that's a true false thing, if that's not true, and we just write all.
Also, the chance that we have not turned the auto filters on at all.
So, if WS dot auto filter is nothing, that's an interesting statement.
I don't think I've used that in the podcast before.
That's a word say, here's an object variable if this has not yet been created.
If it doesn't exist yet, the words for that are, is nothing and we write, I actually just let's write, "no filter apply", filters not turned on and instead of all done here, we'll write (typing) selected.
Alright! So, a couple of tests turn the filters off, update titles, filters not turned on, turn the filter on, but don't select anything from item 6, everything selected and then the other one to be aware of let's turn on Sunny and Raining and Hurricane, click [ OK ], what a weird combination.
And click update titles, it shows us that but then if later on, we would filter another column.
Take the zero out of head one.
Now, Sunny is no longer here.
It's not even in the list anymore, but it's still secretly selected behind the scenes.
I know that because when I run the macro, they continue to show sunny as one of the items.
So, just be aware of that.
The other thing that was frustrating to me as I said hey this update titles would be really good, if we could just run this every time, I chose from the filter, but there does not seem to be a worksheet, event macro.
Selection change, change none of these are getting fired.
When we just change something from the Auto filter drop-down, so worksheet changed there.
If I come in here and type 1, it runs the macro, but if all I do is open the filter drop-down and change what's selected.
That does not fire that, so that's why I had it add the shape here, right click [ assign macro ].
I had to manually assign that macro to the...
Hey! I want to thank Mark for posting a question, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
