That is not possible. The CriteriaRange, like the name says, must always be a range. You have to write the criteria in a range in a worksheet and refer to that range if you want to use the Advanced Filter.
Yes I think this is possible. It's just that I'm rewriting someone else's code to speed it up so was trying to avoid switching vba's attention between vba and the worksheet and then back to vba (I'm not sure how to express that properly!)
I see what you mean. The Advanced Filer is such a powerful and flexible tool that it's a pity that you cannot use it directly in vba using for ex. an array for the data and an array for the criteria. Unfortunately it's not possible.
Your options are:
1 - write code for your specific case
2 - use excel, for ex.:
. add a worksheet (or a workbook)
. copy the data to excel
. execute the filter
. get the result
. delete the worksheet (or the workbook)
or the same using a free range in an existing worksheet.
Notice that you have the same problem with the database functions.
I mean write code that does not use the Advanced Filter but that performs a similar function. An ex., a function might accept an array with the data table, a column index and a criterion value, and would return an array with only the rows where the value in the data table in the specified column satisfies the criterion.
For ex, you have an array vData(1 to 4, 1 to 3) with the values
You would write a function that you could call like
vResult = FilterCol(vData, 3, False)
that would return an array vResult with only the rows 2 and 4 of the vData array, that are the rows where column 3 is equal to False.
This is, of course, a solution with a very limited scope, not a general solution like the Advanced Filter. You would have to design a solution to solve your specific problem.