Store active Autofilter criteria in array or range

[Yard]

Hi all,

I want to perform some operations (basically a secondary filter) based on the values which are currently filtered within a single filter column.

.Autofilter.Filters(n).Criteria1

and

.Criteria2

are great, but what if there are more than 2???

i.e. I have a column containing values L01 to L20.

My column is filtered on L05, L06 and L07 (or some other combination).

I want to extract the values and L05, L06 and L07 and do what I will with them.

What am I missing??

Thanks

 

[Richard Schollar]

Set a Range object to hold the filtered rows and use the Union method to successively add additional rows to it as you apply different filters. Ultimately you will end up with a range object conatining just those rows/values you want and you can then copy and paste this somewhere.

 

[xld]

Create a formula in a helper column that evaluates all of the criteria and returns TRUE or FALSE. Then filter the helper column.

 

[Yard]

Thanks both. Not sure I've explained sufficiently, as I don't quite understand either of the solutions offered.

I don't want the data contained on the rows matching "L05" "L06" or "L07", I just want those 3 values.

Richard, if what you suggested does this, any chance of a quick example to illustrate?

xld, evaluate what criteria? Which worksheet function can refer to Autofilter criteria?

Thanks

 

[Richard Schollar]

What criteria have you applied to the autofilter to result in rows with those quoted values? Couldn't you just use them? If not (say you have used criteria like "does not equal") then you could use a collection to iterate thru the visible cells storing all the unique values. Does this sound like what you want to do?

 

[RoryA]

There only are two criteria for an autofilter, so criteria3 shouldn't really be an issue!

 

[Yard]

I've definitely missed something here! Maybe I should have mentioned I'm using Excel2007? [oops]

In the column I have filtered, there exist the values L01, L02, L03.....L20.

The Autofilter dropdown menu shows all of these values, and I can put check marks against any number or combination of them. This to me equals more than one Autofilter criteria.

If I put checkmarks against L05, L06 and L07 then I want my code to simply return the text values "L05", "L06" and "L07" into an array or a range.

Don't know how else to describe it?!?

Richard,

you could use a collection to iterate thru the visible cells storing all the unique values. Does this sound like what you want to do?

Can you give me a headstart on that one? I did try that, using the AdvancedFilter method on the Visible SpecialCells, but it fails (I guess because the range is non-contiguous).

Thanks for sticking with me, guys - this feels like one of those "why isn't there just a function for this?" situations

 

[RoryA]

Yes, mentioning 2007 would have been helpful!
Are you using a plain autofilter or a table filter?

 

[Yard]

Yep, realized that when I read the replies...sorry! Haven't used 2003 for a good few months now.

Was using the standard table filter, but things started falling over because the filter mode was set to FALSE and my code was just exiting. Took me half an hour to figure out that there must be a different property for the Table filter then??

[ The Filter icon in the Ribbon was still highlighted, so I deactivated it and reactivated it, and now I believe I have a normal autofilter. (God knows how you switch the special Table filter back on....) ]

Thanks Rory

And Richard, your method sounds good for some of the next bits on this project, so would still love to hear what you've got! Cheers

 

[Yard]

bump

Rory, Richard, any other large brains who can help?

Thanks