Using a macro to operate a filter on variable data

Hi,

I have a written a macro to populate a filter criteria with a comma separated list, but the filter fails to work as each individual item on the list needs to be surrounded by speech marks (") and I don't know how to do this.

This may not be an actual vba issue?

The list I am using is derived from =SUBSTITUTE(TRIM(BK9&" "&BK15&" "&BK21)," ",",") There are 120 different cells that this formula references that could be blank. The SUBSTITUTE TRIM functions allow me to just use the cells that have data in. But I can't seem to get the " symbols in there. (I've tried the TEXT function on the original cells, & this doesn't work.


Any ideas?

Thanks

Thanks Kaper. This worked, but the macro didn't .

I'll try & explain what I'm trying to do as I cannot attach a mini workbook due to the complexity of the document I am working on (I can't simplify it enough):

I am trying to replicate the array on an Excel Autofilter as part of a macro. Below is a sample of the filter array:


ActiveSheet.Range("$A$60:$ACQ$6356").AutoFilter Field:=1, Criteria1:=Array( _
"01103032", "01103033", "01103034", "01103035"), Operator:=xlFilterValues

There are 4 items in the array shown, but I would want to filter up to 120 items. I've tried to replicate the array as a string called 'skurange' as you can see below:

ActiveSheet.Range("$A$60:$ACQ$6356").AutoFilter Field:=1, Criteria1:=Array( _
"" & skurange & ""), Operator:= _
xlFilterValues

This doesn't seem to work however as the filter returns no data.( I had already spotted that there is a space betwen the comma and the speechmarks & corrected this in the substitute function)

Any ideas?

Thanks in advance.