Well.., that's what I did the first time I tried it because I used

"B3:O3" as the range address. This didn't work out so well in terms of

a 6 x 12 table so I revised it as posted, putting each range into

separate arrays.

I think using separate arrays for the sections is the best approach,

IMO, and so here's a revision that includes your empty columns...

Sub CombineData2()

Dim sec1, sec2, sec3, n&, lRows&, lCols&, vaDataOut()

Const iStep1% = 5: Const iStep2% = 10

'Load ranges into arrays

sec1 = Range("$B$3:$E$3")

sec2 = Range("$G$3:$J$3")

sec3 = Range("$L$3:$O$3")

'Get the number of rows/cols for the output

lRows = (UBound(sec1, 1) + UBound(sec2, 1) + UBound(sec3, 1)) * 2

lCols = UBound(sec1, 2) + UBound(sec2, 2) + UBound(sec3, 2) + 2

'Resize the output array

ReDim vaDataOut(1 To lRows, 1 To lCols)

'Load the output array

For n = 1 To UBound(sec1, 2) '//fortunately they're all the same

'sec1

vaDataOut(1, n) = sec1(1, n): vaDataOut(2, n) = sec1(1, n)

vaDataOut(3, n + iStep1) = sec1(1, n): vaDataOut(4, n + iStep2) =

sec1(1, n)

vaDataOut(5, n + iStep1) = sec1(1, n): vaDataOut(6, n + iStep2) =

sec1(1, n)

'sec2

vaDataOut(1, n + iStep1) = sec2(1, n): vaDataOut(2, n + iStep2) =

sec2(1, n)

vaDataOut(3, n) = sec2(1, n): vaDataOut(4, n) = sec2(1, n)

vaDataOut(5, n + iStep2) = sec2(1, n): vaDataOut(6, n + iStep1) =

sec2(1, n)

'sec3

vaDataOut(1, n + iStep2) = sec3(1, n): vaDataOut(2, n + iStep1) =

sec3(1, n)

vaDataOut(3, n + iStep2) = sec3(1, n): vaDataOut(4, n + iStep1) =

sec3(1, n)

vaDataOut(5, n) = sec3(1, n): vaDataOut(6, n) = sec3(1, n)

Next

'Dump the data back into the wks

Range("$B$10").Resize(lRows, lCols) = vaDataOut

End Sub

--

Garry

Free usenet access at

http://www.eternal-september.org
Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion