How to create One-dimensional array from Excel Range?

How to create One-dimensional array from Excel Range?

Asked on October 27, 2018 in VBA.
Add Comment


  • 3 Answer(s)

    This code will be useful:

    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
    

     

    Answered on October 27, 2018.
    Add Comment

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

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

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

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

     

    Answered on October 27, 2018.
    Add Comment

           If we read values from a single column into an array as you have it then
    I think it will end up with an array that needs to be accessed using array(1, n) syntax.

    And then 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 sureWe 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
    

     

    Answered on October 27, 2018.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.