Create an Array via VB

[Jak]

Hi all

I am wondering if it is possible to create an array from the data in a column. ie on a spreadsheet in column 1 I have a lot of data. I want to convert it from ie

1045
2145
39283
etc

to an array as dipicted below ie Quotes and Commas and save it to clipboard so that I can paste it directly to my VB code. Any suggestions welcomed.

myArray = Array("1045", "2145", "39283", "1053", "48302", "48944", "48945", "48946", "14119", "2248", "59010", _
            "20111", "10089", "20089", "2305", "37430", "55322", "1903", "2272", "44950", "933", "48217", "10119", "2411", "42255", _
            "2377", "41244  G6", "41244", "52324", "726492", "072132", "49932", "2235", "1090", "2126", "1057", "5133", "20104 H", _
            "20104", "020104D", "20239", "25288", "58653", "020250", "30405", "2116", "58621", "39106", "44325", "32135", "21902", _
            "001088", "000837", "019311", "050645", "035219", "000602", "1026", "1963")

 

[Richard Schollar]

Hi

You can create a variant array directly:

Dim vArray As Variant
 
vArray = Range("A1:A100")
 
MsgBox vArray(50,1)   'returns value that was in A50

Note that this creates a 2-dimensional array (not a 1-D one) so you always have to specify the second dimension (ie vArray(50,1) above.

if you desperately wanted to turn it into a 1-D one you can use the Worksheetfuncton Transpose.

 

[Jak]

Hi Richard

Thanks for the info and example.