Set autofilter criteria based on changing array

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

prods = rng.Offset(0, 4).SpecialCells(xlCellTypeVisible).Value
Sheets("ORDER").Range("A1:CH" & Sheets("ORDER").Range("E65536").End(xlUp).Row).AutoFilter Field:=5, Criteria1:=product, Criteria2:=Array(prods)

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

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

Hi AB33,

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

Dim rng2 as Range
Set rng2 = rng.Offset(0, 4).SpecialCells(xlCellTypeVisible)
prods = rng2.Value

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?

Originally Posted by :) Sixthsense :)

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?

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

Static oClipBoard   As Object
    Dim vArray          As Variant
   
    If oClipBoard Is Nothing Then Set oClipBoard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
   
    rng.Offset(0, 4).SpecialCells(xlCellTypeVisible).Copy
    oClipBoard.GetFromClipboard
    vArray = Split(oClipBoard.GetText, vbCrLf)
    ReDim Preserve vArray(LBound(vArray) To UBound(vArray) - 1)

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

Sheets("ORDER").Range("A1:CH" & Sheets("ORDER").Range("E65536").End(xlUp).Row).AutoFilter Field:=5, Criteria1:=array(varray), Operator:=xlFilterValues

Sets the filter as expected.

Awesome, thanks for your help, guys!