Tuesday, June 16, 2020

VBA Array and Excel Range date exchange

Case I Range Data Passed to Array

Input Range Data

18 40 27 39 44 19
30 24 30 22 41 46
30 25 44 27 39 53
13 45 33 53 55 12
16 21 51 18 46 42

Sub RangeToArray()
    Dim rng As Range
    Dim arr As Variant
    Dim r As Long
    Dim c As Long
    Dim RowData As Variant
    Set rng = Range("A1:F5")
    arr = rng
'    Debug.Print UBound(arr, 1)    ''' row size
'    Debug.Print UBound(arr, 2)    ''' column size
    
    For r = LBound(arr) To UBound(arr)
        
        For c = LBound(arr, 2) To UBound(arr, 2)
            RowData = RowData & vbTab & arr(r, c)
        Next
        Debug.Print RowData
        RowData = Null
    Next
    Set rng = Range("A7:F11")
    ''pass array data to Range Case II Array Passed to Range Data

    rng = arr

End Sub

Output: 

    18  40  27  39  44  19
    30  24  30  22  41  46
    30  25  44  27  39  53
    13  45  33  53  55  12

    16  21  51  18  46  42











No comments:

Post a Comment

Hot Topics