How to create a string

[azizrasul]

I have the following code.

    ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array("ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"), Operator:=xlFilterValues

The values ELDU238 - ELDU247 are values in a column beginning at A2. I want to create

"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"

in a string variable strArray which will enumerate whatever values are in column A beginning at A2 and create the above. I can then have

    ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array(strArray), Operator:=xlFilterValues

Hence if the number of entries changes e.g. say I have

"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247", "ELDU248"

the code will still work.

 

[Norie]

Why not create an array instead of a string?

Then you wouldn't need to use Array().

 

[azizrasul]

Any idea how I would go about doing this?

 

[Norie]

Something like this.

Dim rng As Range
Dim arrCrit
    Set rng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    
    arrCrit = Application.Transpose(rng)

 

[azizrasul]

Thanks Norie, that worked a treat.

Just 1 more thing. If I have a separate criteria but this time the data is in a row i.e. A2, B2, C2, etc. how would the line

Set rng = Sheets("After Sales Courses").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).row)

change?