vba - One-dimensional array from Excel Range

Question

I'm presently populating my array Securities with the following code:

Option Base 1
Securities = Array(Worksheets(3).Range("A8:A" & SymbolCount).Value)

This produces a 2-dimensional array where every address is (1...1,1...N). I want a 1-dimensional array (1...N).

How can I either (a) populate Securities as a 1-dimensional array, or, (b) efficiently strip Securities to a 1-dimensional array (I'm stuck at a with each loop).

Answer 1

I know you already accepted an answer but here is simpler code for you:

If you are grabbing a singe row (with multiple columns) then use:

Securities = application.transpose(application.transpose _
             (Worksheets(3).Range("A8:A" & SymbolCount).Value))

If you are grabbing a single column (with multiple rows) then use:

Securities = application.transpose(Worksheets(3).Range("A8:A" & SymbolCount).Value)

So, basically you just transpose twice for rows and once for columns.

Update:

Large tables might not work for this solution (as noted in the comment below):

I used this solution in a large table, and I found that there is a limitation to this trick: Application.Transpose(Range("D6:D65541").Value) 'runs without error, but Application.Transpose(Range("D6:D65542").Value) 'run-time error 13 Type mismatch

Update 2:

Another problem you might have as mentioned in the comments:

If one exceeds 255 characters, the function fails.

It has been a long time since I worked with Excel VBA but this might be a general limitation of accessing the data this way?