Transpose Row by Row

[walan]

Does anyone have a shorter/simpler way of transposing more than 150 rows of data that is in 5 columns into one column? The only catch or difficulty is it has to be in the order of the rows vertically. See below.
12 15 45 20 12
13 15 45 20 15
14 15 45 20 45
16 15 45 20 20
17 15 45 20 13
18 15 45 20 15
19 15 45 20 45
20 15 45 20 20
21 15 45 20 14
15
45
20
16
15
45
20
17
15
45
20
18
15
45
20

I did create a macro which is repetitive and was wondering if anyone can help make is simpler this way it transpose more than 100 rows of data into one column.
Range("A1:D1").Select
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,SkipBlanks:=False , Transpose:=True
Application.CutCopyMode = False
Range("A2:D2").Select
Selection.Copy
Range("F5").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A3:D3").Select
Selection.Copy
Range("F9").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A4:D4").Select
Selection.Copy
Range("F13").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A5:D5").Select
Selection.Copy
Range("F17").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A6:D6").Select
Selection.Copy
Range("F21").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("E1").Select
End Sub

[Peo Sjoblom]

Assume your data starts A1

=INDEX($A$1:$D$150,FLOOR(ROWS($A$1:A4)/4,1),MOD(ROWS($A$1:A4),4)+1)

copy down

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"walan" <[email protected]> wrote in message
news:[email protected]...
>
> Does anyone have a shorter/simpler way of transposing more than 150 rows
> of data that is in 5 columns into one column? The only catch or
> difficulty is it has to be in the order of the rows vertically. See
> below.
> 12 15 45 20 12
> 13 15 45 20 15
> 14 15 45 20 45
> 16 15 45 20 20
> 17 15 45 20 13
> 18 15 45 20 15
> 19 15 45 20 45
> 20 15 45 20 20
> 21 15 45 20 14
> 15
> 45
> 20
> 16
> 15
> 45
> 20
> 17
> 15
> 45
> 20
> 18
> 15
> 45
> 20
>
> I did create a macro which is repetitive and was wondering if anyone
> can help make is simpler this way it transpose more than 100 rows of
> data into one column.
> Range("A1:D1").Select
> Selection.Copy
> Range("F1").Select
> Selection.PasteSpecial Paste:=xlAll,
> Operation:=xlNone,SkipBlanks:=False ,
> Transpose:=True
> Application.CutCopyMode = False
> Range("A2:D2").Select
> Selection.Copy
> Range("F5").Select
> Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
> SkipBlanks:=False _
> , Transpose:=True
> Application.CutCopyMode = False
> Range("A3:D3").Select
> Selection.Copy
> Range("F9").Select
> Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
> SkipBlanks:=False _
> , Transpose:=True
> Application.CutCopyMode = False
> Range("A4:D4").Select
> Selection.Copy
> Range("F13").Select
> Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
> SkipBlanks:=False _
> , Transpose:=True
> Application.CutCopyMode = False
> Range("A5:D5").Select
> Selection.Copy
> Range("F17").Select
> Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
> SkipBlanks:=False _
> , Transpose:=True
> Application.CutCopyMode = False
> Range("A6:D6").Select
> Selection.Copy
> Range("F21").Select
> Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
> SkipBlanks:=False _
> , Transpose:=True
> Application.CutCopyMode = False
> Range("E1").Select
> End Sub
>
>
> --
> walan
> ------------------------------------------------------------------------
> walan's Profile:
> http://www.excelforum.com/member.php...o&userid=13528
> View this thread: http://www.excelforum.com/showthread...hreadid=531240
>