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=531241

Hi Walan,

Have a look at:
http://www.geocities.com/davemcritch...l/snakecol.htm

- I haven't tried it but it may help you.

Hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=531241

Your code only does 4 columns. I did this to handle 5 as you stated. Also,
yours seems to overwrite some of the copied data. This doesn't. So test it
on a copy of your data.

Sub CCC()
Dim rng As Range, i As Long
i = 1
Set rng = Range(Cells(1, 1), Cells(Rows.Count, _
1).End(xlUp))
For Each cell In rng
cell.Resize(1, 5).Copy
Cells(i, 6).PasteSpecial Paste:=xlValues, _
Transpose:=True
i = i + 5
Next
End Sub

--
Regards,
Tom Ogilvy


"walan" wrote in message
...

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=531241

Try:

Sub TestTranspose()
Dim r As Range, c As Range, c2 As Range
Set r = Range(Range("A1"), Range("A1").End(xlDown))
Set c2 = Range("F1")
For Each c In r.Cells
c2.Resize(4, 1) = Application.Transpose(c.Resize(1, 4))
Set c2 = c2(5)
Next
End Sub

Regards,
Greg


"walan" wrote:


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=531241

Either I don't understand what you are trying to do, or your example as well
as your code does not agree with what you say you want to do.
If you take all 5 columns of one row and copy/transpose it to F1, then
that one row after paste will occupy F1:F5. If you then do the same thing
with the next row but paste it to F5, you will be wiping out the last entry
of the previous paste. Is this correct or did I miss something? HTH
Otto
"walan" wrote in message
...

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=531241

Tom, Greg -The two macros worked perfect. No overwrites et all. You guys
are awesome.


--
walan
------------------------------------------------------------------------
walan's Profile: http://www.excelforum.com/member.php...o&userid=13528
View this thread: http://www.excelforum.com/showthread...hreadid=531241