Getting data into an array

[xp]

I have three discontiguous ranges, each with the same number of columns, but
each may have a different number of rows.

I want to copy the data from each of the ranges into a single array
preserving their relative row/column positions from the source.

When the array is written out, it should write the data into cells in the
same relative position, but as a single contiguous range.

So, for example, if the source has three sets of selections, each 5 columns
wide, one has two rows, one has three rows, and one has four rows, then the
data should write out as a single contiguous range having 9 rows and 5
columns.

Mostly, I need help with getting the data into the array, I think I can take
care of the write out.

Thanks much for your help!

 

[Rick Rothstein]

Are these the same five columns, but different row ranges within them? If
not, then what determines the order of placement... rows or columns? I ask
this question because you could have ranges that include, as an example,
these two ranges (assume the third range is lower down and to the right of
them)... B6:F9 and H2:L4... which of these is considered *first* in order
(to determine the "relative position")?

 

[Joel]

Do you really want the data in an array or just write it to the worksheet in
order. How are you specifying the different selections. Usually when I seee
something like this it is usually making a summary sheet where the data
starts in the same location on 2 or more different sheets and the data is
combined on a new summary sheet.

 

[Dan]

I don't have the full solution but the following code can be used to obtain
the correctly sized array...

Sub Create_Array()

'highlight the three ranges

Dim r As Range
Dim row As Integer, col As Integer
Dim arr() As Integer

For Each r In Selection.Areas
If col < r.Columns.Count Then
col = r.Columns.Count
End If
row = row + r.Rows.Count
Next r

ReDim arr(row, col) As Integer


End Sub