One-dimensional array from Excel Range

Solution 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?

Solution 2:

Sub test2()
    Dim arTmp
    Dim securities()
    Dim counter As Long, i As Long
    arTmp = Range("a1").CurrentRegion
    counter = UBound(arTmp, 1)
    ReDim securities(1 To counter)
    For i = 1 To counter
        securities(i) = arTmp(i, 1)
    Next i
    MsgBox "done"
End Sub

Solution 3:

This will reflect the answer iDevlop gave, but I wanted to give you some additional information on what it does.

Dim tmpArray As Variant
Dim Securities As Variant

'Dump the range into a 2D array
tmpArray = Sheets(3).Range("A8:A" & symbolcount).Value

'Resize the 1D array
ReDim Securities(1 To UBound(tmpArray, 1))

'Convert 2D to 1D
For i = 1 To UBound(Securities, 1)
    Securities(i) = tmpArray(i, 1)
Next

Probably the fastest way to get a 1D array from a range is to dump the range into a 2D array and convert it to a 1D array. This is done by declaring a second variant and using ReDim to re-size it to the appropriate size once you dump the range into the first variant (note you don't need to use Array(), you can do it as I have above, which is more clear).

The you just loop through the 2D array placing each element in the 1D array.

I hope this helps.