[RESOLVED] RangeToArray doesn't transfer all data?

[ChaosTamed]

Hi All,

I have a table of data with 13 rows and 6 columns. I want to transfer the data from columns 1, 3 and 5 into an array for processing.

I have used the following script:

arrValue = Empty
arrValue = Worksheets(1).Range("B3:B15,D315,F3:F15").Value
RangeToArray = True

I was hoping this would result in an array arrValue with dimensions (13,3) but when I try to access any element in column 2 or 3 I get a subscript out of range error. I thought that maybe the range was being copied as a single column but when I checked rown 14, column 1 I also got an out of range error.

Any ideas where I'm going wrong?

Thanks
Matt

[westconn1]

i think you can only assign a contiguous range to an array

arrvalue, in your example, is dimensioned
arrvalue(1 to 13, 1 to 1)

[ChaosTamed]

Thanks for your speedy reply. That would certainly explain it. If this is the case, is it possible to concatenate 2 1-D arrays to form 1 2-D array?

Thanks
Matt

[westconn1]

yes, but any array assigned from a range is always 2d, even when only one column or row

it may be possible using copymemory, otherwise loop through the array

Dim arr()
arr = Range("B3:B15")
ReDim Preserve arr(1 To 13, 1 To 2)
For i = 1 To 13
    arr(i, 2) = Cells(i + 2, 4)
Next

to work dynamically use ubounds of the array
you can only redim preserve the last dimension of a multi dimensional array

[ChaosTamed]

That's great. Works perfectly. Thanks for your help.