Transposing multiple rows into single column

[gaurar]

Hi,

I need help to convert multiple rows of data into single column question is, here data varies from row to row

Row 1: a b c
Row 2: 1 2 3 4
Row 3: a b c d e

Result needed:

a
b
c
1
2
3
4
a
b
c
d
e

 

[JLGWhiz]

This assumes you have less than or equalt to 16.384 cells of data in your data set.

Sub t()
    For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
        Range(c, Cells(c.Row, Columns.Count).End(xlToLeft)).Cut Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
    Next
    Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Copy
    Range("A2").PasteSpecial xlPasteValues, Transpose:=True
    Rows(1).Delete
End Sub

 

[JLGWhiz]

Here is an alternative method if you do have a large data set.

Sub t()
For i = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row To 1 Step -1
    cnt = Range(Cells(i, 1), Cells(i, Columns.Count).End(xlToLeft)).Columns.Count
    txp = Application.Transpose(Range(Cells(i, 1), Cells(i, Columns.Count).End(xlToLeft)))
    Rows(i).ClearContents
    Cells(i + 1, 1).Resize(cnt - 1).Insert xlShiftDown
    Cells(i, 1).Resize(cnt) = txp
Next
End Sub

 

[gaurar]

Many thnks for your prompt response, this is resolved!!!

 

[JLGWhiz]

Many thnks for your prompt response, this is resolved!!!

You are welcome,
regards, JLG