One way...


Sub ColBlocksToRows()
Dim rng As Range
Dim n&, lRow&, lLastRow&, vArr()
lRow = 1 '//initialize for counter increment
lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
Do
lRow = lRow + 10
Set rng = Cells(lRow, 1).Resize(9, 1)
If WorksheetFunction.CountA(rng) 0 Then
vArr(UBound(vArr)) = Join(rng, vbTab)
ReDim Preserve vArr(UBound(vArr) + 1)
End If 'CountA(rng) 0
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) = Split(vArr(n), vbTab)
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