Arrange data spanning 8 columns and 3 rows to 24 columns and 1 row
Assuming you want a1:H1 and A2:H2 and A3:H3 copied across 24 columns use this
macro.
Sub Move_Sets()
Dim iSource As Long
Dim iTarget As Long
iSource = 1
iTarget = 1
Do
Cells(iSource, "A").Resize(1, 8).Cut _
Destination:=Cells(iTarget, "A")
Cells(iSource + 1, "A").Resize(1, 8).Cut _
Destination:=Cells(iTarget, "I")
Cells(iSource + 2, "A").Resize(1, 8).Cut _
Destination:=Cells(iTarget, "Q")
iSource = iSource + 3
iTarget = iTarget + 1
Loop Until IsEmpty(Cells(iSource, "A").Value)
End Sub
Gord Dibben MS Excel MVP
On Mon, 19 Mar 2007 12:38:07 -0700, pfdino
wrote:
I know the transpose option under Paste Special menu will arrange data from
horizontal to vertical and vice versa. Is there a function or formula that
will re-arrange data from multiple columns/rows into one row? For example,
from 8 columns across and 3 rows down to 24 columns across and 1 row down?
(I have 1000 rows of data, so manually would be a time-consuming process).
Thank you.
|