Set autofilter criteria based on changing array

[JasperD]

Hi guys and girls,

I have a sheet where I need to place an autofilter.
Criteria1 is fixed as always being just 1 criteria and always a product number.
Criteria2 is flexible and comes from the products which are related to criteria 1.

I set a filter on a sheet on criteria 1 and subsequently I have a column where just the related products are visible.
How can I capture only the visible values so that I can put

Please Login or Register  to view this content.

Can I store the related products in a variable and just say Criteria2:=Array(relproducts) or something?

I know it's a bit vague, but I hope you catch my drift!
Thanks,

Jasper

[LokeshKumar]

Hey
Upload the sample sheet will do some change as per you

[JasperD]

I can't upload a sample.
I'm trying to do something like this:

Please Login or Register  to view this content.

Where rng = $B$2:$B$116193

But this doesn't work... What am I doing wrong here?

[AB33]

Please Login or Register  to view this content.

Excel treats the visible cells as "Areas", so you only get the areas value, it may be 1 or 2 rows in to an array.
What I have not tried is if excel accepts a range and an array filter on a single row- filter. I am not sure if this also is the issue.

[JasperD]

Hi AB33,

correct, I only get 1 value - I also try to do something like this:

Please Login or Register  to view this content.

To no avail, even though rng2 is now set as :
$F$47147,$F$47362,$F$47634,$F$48035,$F$48490,$F$48892,$F$49350,$F$49753,$F$50211,$F$50616,$F$51075,$F$51480,$F$51939,$F$52344,$F$52801,$F$53205,$F$53658,$F$54062,$F$54512,$F$54916,$F$55366,$F$55772,$F$56226,$F$56650,$F$57100,$F$57507,$F$57969,$F$58388

I thought I had to transpose them as well (application.transpose(rng2.value)) but that doesn't work either.
How would I go around this?

Or do I have to run a quick loop on rng2 and just manually add those values into an array? But if I do that, what would be the syntax?

[:) Sixthsense :)]

Store the particular column/Field visible cells values in an array and use xlFilterValues in operator

[AB33]

JasperD,
You can cheat excel, but not the way you tried it. One other option is to copy the visible cells in to a sheet- as a range- and convert this values in to an array.

[:) Sixthsense :)]

Check whether this helps you

http://www.excelforum.com/excel-prog...utofilter.html

[JasperD]

Store the particular column/Field visible cells values in an array and use xlFilterValues in operator

Hi SixthSense,

thanks for your reply! This is exactly what I want, but how do I store the particulare visible cells values into an array?

[:) Sixthsense :)]

but how do I store the particulare visible cells values into an array?

I think you have to loop through the cells to check whether the row is visible or not.

If it is visible then you have to store it in array with the help of If routine.

http://dailydoseofexcel.com/archives...d-list-in-vba/

[JasperD]

Me being lazy and don't wanting to loop, I took the following approach:

Please Login or Register  to view this content.

This perfectly well puts all the cells that I need into an array.
And

Please Login or Register  to view this content.

Sets the filter as expected.

Awesome, thanks for your help, guys!