Transpose 3 columns into Single column

A

AncientLearner

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.
 
D

Dave D-C

Just add the second line here:
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?)
 
A

AncientLearner

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.

:)
 
A

AncientLearner

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.
 
D

Dave D-C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top