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).
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, butApplication.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?
Application.Transpose(Range("D6:D65541").Value) 'runs without error, but Application.Transpose(Range("D6:D65542").Value) 'run-time error 13 Type mismatch
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 
arTmp = Range("A8:A" & SymbolCount) as wellThis 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.
ReDim Preserve the original 2D matrix?If you read values from a single column into an array as you have it then I do think you will end up with an array that needs to be accessed using array(1, n) syntax.
Alternatively, you can loop through all cells in your data and add them into an array:
Sub ReadIntoArray() Dim myArray(), myData As Range, cl As Range, cnt As Integer, i As Integer Set myData = Worksheets(3).Range("A8:A" & SymbolCount) //Not sure how you get SymbolCount ReDim myArray(myData.Count) cnt = 0 For Each cl In myData myArray(cnt) = cl cnt = cnt + 1 Next cl For i = 0 To UBound(myArray) //Print out the values in the array as check... Debug.Print myArray(i) Next i End Sub 
