excel - 2 Dimensional array from range

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

Answer 1

Assuming your spreadsheet looks kind of like this

There is a really easy way to stick that in a 2D array

Dim arr as Variant
arr = Range("B6:H14").Value

The easiest way to print this array back to spreadsheet

Sub PrintVariantArr()

    Dim arr As Variant
    arr = Range("B6:H14")

    Range("B16").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

End Sub

Or you can iterate/loop the array

Sub RangeToArray()

    Dim arr As Variant
    arr = Range("B6:H14").Value
    Dim r As Long, c As Long

    r = 16
    c = 2

    Dim i, j
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            Cells(r, c) = arr(i, j)
            c = c + 1
        Next j
        c = 2
        r = r + 1
    Next i

End Sub

And your array printed back to the spreadsheet