2 Dimension array out of range

Question

I have text data in an Excel spreadsheet in cells B6:H14 .

Some rows will have 2 cells with content, others 4 and some 7. How do I copy them to a 2-dimensional array? I already know the sizes already, I'm fine with sizes that are not declared by the dynamic code.

Do I need to use a loop (which I am planning to use now)?

Or is there an easier / more elegant way?

Answer 1

Assuming your table looks like this

There is a very easy way to insert this into a 2D array.

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

The easiest way to print this array back to the 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 / quote an 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 is printed in a spreadsheet