4

I have text data in Excel worksheet in the cells B6:H14.

Some rows will have 2 cells with contents while others have 4 and some will have 7. How do I copy these to a 2 dimensional array? I know the dimensions already and so, I am ok with the dimensions not being declared dynamic code.

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

Or is there an easier / more elegant way?

Community
  • 1
  • 1
Siddhartha
  • 147
  • 1
  • 2
  • 9
  • 4
    I downvoted this as you don't appear to have tried anything before requesting help. There are a number of existing answers such as http://stackoverflow.com/q/13663370 which will help. – brettdj Dec 19 '14 at 09:52
  • @brettdj actually that exact question you linked to asks about outputting a 2D array into a range - this question is about doing that in reverse. – Richard Le Mesurier May 06 '15 at 07:19
  • 1
    @RichardLeMesurier that question shows how to put a range into an array, manipulate it, and dump it back to a range - ie it does both range to array (which this question asked), and array to range. Which is the same approach Mehow/VBA4All/It's been a Pleasure took in answering it – brettdj May 06 '15 at 08:43
  • 1
    @brettdj fair enough - I just feel that someone without your or my VBA knowledge may have had trouble realising that. They are both good answers tho. – Richard Le Mesurier May 06 '15 at 09:34
  • @RichardLeMesurier yes - you do gave a good point. – brettdj May 06 '15 at 13:07

1 Answers1

46

Assuming your spreadsheet looks kind of like this

spreadsheet

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

result