Criteria = Array used in an Autofilter

[rirus]

I am trying to run this autofilter using an array as the criteria1.

FILTER:
ActiveSheet.ListObjects("Table_Query_from_Query1").Range.AutoFilter _
Field:=40, Criteria1:=Array(strCritAry), Operator:=xlFilterValues

The string, strCritAry is the input of selected data from Column 40 used to filter the spreadsheet.

strCritAry = ""Data(1)", "Date(2)", "Data(4)""

This doesn't work. It should be without the double quotes enclosing the string.

strCritAry = "Data(1)", "Date(2)", "Data(4)"

MY QUESTION:
what can be used instead of the string, strCritAry which encapsulates the array in double quotes or is there another way to execute an autofilter where the filtered data is updated by another program.

Thanks,


Rirus

 

[Domenic]

Try...

strCritAry = Array("Data(1)","Date(2)","Data(4)")

 

[rirus]

Nope:

First I tried it using arrays, strCrit(X)...

strCritAry = Array("""" & strCrit(0) & """", """" & strCrit(1) & """")

strCritAry wouldn't populate

Next I tried the actual values,

strCritAry = Array("Data1(LA)", "Date1(NA)")

strCritAry Still wouldn't populate.

I thought that would work. Do I need to define the strCritAry?

Regards,

rirus

 

[Domenic]

If the array strCritAry is defined as follows...

strCritAry = Array("Data1","Data2","Data3")

To filter for specific elements within the array:

Criteria1:=Array(strCritAry(0), strCritAry(2))

To filter for all elements within the array:

Criteria1:=strCritAry

 

[rirus]

Got it... that worked.

Thanks... this awesome. It has opened a lot of things I can do with filtering.

One more thing... If the Data1, Data2, and Data3 values change in the column, how can I redefine the strCritAry Array each time the spreadsheet is opened?

Thanks for your help... I am learning a lot about filters.


Rirus

 

[tusharm]

You can populate an array as required. It isn't only filters that are worth learning about. {grin}

One untested example:

dim MyArr(2) as string
myarr(0)="hello"
myarr(1)="there"
myarr(2)=cells(1,1).value

Got it... that worked.

Thanks... this awesome. It has opened a lot of things I can do with filtering.

One more thing... If the Data1, Data2, and Data3 values change in the column, how can I redefine the strCritAry Array each time the spreadsheet is opened?

Thanks for your help... I am learning a lot about filters.


Rirus

 

[rirus]

I set up the arrays with autofilters and they are working great.... it took a bit but I figured it out.

Thank you for the help.... Arrays are fun too especially when combined with autofilters.


rirus