Hi Garry,
Hmm, I get a subscript out of range on this line
vArr(UBound(vArr)) = Join(rng, vbTab)
Howard
Hmm...! Can't imagine what I was thinking since loading into a var
typed 'As Range' results a 2D array. This works...
Sub ColBlocksToRows()
' Transposes blocks of column data to row data.
' Loads each column block into an array as an array of data,
' resulting in an array of arrays.
' The array is dumped into the worksheet 1 row at a time.
Dim n&, lRow&, lLastRow&, vArr()
lRow = 11 '//initialize to start row
lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
Do
ReDim Preserve vArr(n)
With Application
vArr(n) = .Transpose(.Index(Cells(lRow, "A").Resize(9), 0, 1))
End With 'Application
n = n + 1: lRow = lRow + 10
Loop While lRow < lLastRow
lRow = 1 '//initialize for counter increment
For n = LBound(vArr) To UBound(vArr)
lRow = lRow + 1
Range("D" & lRow).Resize(1, 9) = vArr(n)
Next 'n
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