Transpose 3 columns into Single column
 

I have 3 columns of data, the numbers of rows is variable. I am trying the
transpose the data from each row into a single column.

A1 B1 C1
Parts# Qty Sor
12345 1 011
67890 10 012

Desired result:
Parts#
Qty
Sor
12345
1
011
67890
10
012

I have been using the following but can't seem to get the 3rd column to move.

Sub test()
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range

With Sheets("Sheet1")
Set rngFrom = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
End With

Set rngTo = Sheets("Sheet2").Range("A2")

For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value
Set rngTo = rngTo.Offset(4, 0)
Next rng

End Sub

Any suggestions?

Thanks.
--
AncientLearner
....never to old to learn something new

Transpose 3 columns into Single column
 

Just add the second line he
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value
rngTo.Offset(2, 0).Value = rng.Offset(0, 2).Value
(Is this a trick question?)

AncientLearner wrote:
I have 3 columns of data, the numbers of rows is variable. I am trying the
transpose the data from each row into a single column.

A1 B1 C1
Parts# Qty Sor
12345 1 011
67890 10 012

Desired result:
Parts#
Qty
Sor
12345
1
011
67890
10
012

I have been using the following but can't seem to get the 3rd column to move.

Sub test()
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range

With Sheets("Sheet1")
Set rngFrom = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
End With

Set rngTo = Sheets("Sheet2").Range("A2")

For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value
Set rngTo = rngTo.Offset(4, 0)
Next rng

End Sub

Any suggestions?

Thanks.

Transpose 3 columns into Single column
 

Thanks Dave, and no, it is not a trick question, just trying to get a handle
on a the number of columns variable. So, is it also logical the for
additional columns to be:
rngTo.Offset(3, 0).Value = rng.Offset(0, 3).Value
rngTo.Offset(4, 0).Value = rng.Offset(0, 4).Value
rngTo.Offset(5, 0).Value = rng.Offset(0, 5).Value

Just an OlderDude trying to learn NewTricks. Thanks again for your patience.

:)

--
AncientLearner
....never to old to learn something new


"Dave D-C" wrote:

Just add the second line he
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value
rngTo.Offset(2, 0).Value = rng.Offset(0, 2).Value
(Is this a trick question?)

AncientLearner wrote:
I have 3 columns of data, the numbers of rows is variable. I am trying the
transpose the data from each row into a single column.

A1 B1 C1
Parts# Qty Sor
12345 1 011
67890 10 012

Desired result:
Parts#
Qty
Sor
12345
1
011
67890
10
012

I have been using the following but can't seem to get the 3rd column to move.

Sub test()
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range

With Sheets("Sheet1")
Set rngFrom = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
End With

Set rngTo = Sheets("Sheet2").Range("A2")

For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value
Set rngTo = rngTo.Offset(4, 0)
Next rng

End Sub

Any suggestions?

Thanks.

Transpose 3 columns into Single column
 

Thanks again, I think I am beginning to see the light, I've tweaked it to 5
columns and it works with several thousand rows. Your are the BEST.

--
AncientLearner
....never to old to learn something new


"AncientLearner" wrote:

I have 3 columns of data, the numbers of rows is variable. I am trying the
transpose the data from each row into a single column.

A1 B1 C1
Parts# Qty Sor
12345 1 011
67890 10 012

Desired result:
Parts#
Qty
Sor
12345
1
011
67890
10
012

I have been using the following but can't seem to get the 3rd column to move.

Sub test()
Dim rngFrom As Range
Dim rngTo As Range
Dim rng As Range

With Sheets("Sheet1")
Set rngFrom = .Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
End With

Set rngTo = Sheets("Sheet2").Range("A2")

For Each rng In rngFrom
rngTo.Value = rng.Value
rngTo.Offset(1, 0).Value = rng.Offset(0, 1).Value
Set rngTo = rngTo.Offset(4, 0)
Next rng

End Sub

Any suggestions?

Thanks.
--
AncientLearner
...never to old to learn something new

Transpose 3 columns into Single column
 

Just an OlderDude trying to learn NewTricks. Thanks again for your patience.

Well isn't this group a great place for that!

In case your columns get larger than 5, I would:
Option Explicit

Sub Sub1()
Dim iRowFmZ&, iRowFm&, iColFm&, iRowTo&
iRowFmZ = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
iRowTo = 1
For iRowFm = 2 To iRowFmZ
For iColFm = 1 To 5
Sheets("Sheet1").Cells(iRowFm, iColFm).Copy _
Sheets("Sheet2").Cells(iRowTo, 1)
iRowTo = iRowTo + 1
Next iColFm
iRowTo = iRowTo + 1 ' skip?
Next iRowFm
End Sub

AncientLearner wrote:
Thanks Dave, and no, it is not a trick question, just trying to get a handle
on a the number of columns variable. So, is it also logical the for
additional columns to be:
rngTo.Offset(3, 0).Value = rng.Offset(0, 3).Value
rngTo.Offset(4, 0).Value = rng.Offset(0, 4).Value
rngTo.Offset(5, 0).Value = rng.Offset(0, 5).Value