# Transposing multiple rows into single column

#### gaurar

##### New Member
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

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This assumes you have less than or equalt to 16.384 cells of data in your data set.

Code:
``````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``````

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

Code:
``````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``````

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

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

You are welcome,
regards, JLG

Replies
6
Views
182
Replies
2
Views
320
Replies
18
Views
254
Replies
2
Views
508
Replies
1
Views
328

Threads
1,196,248
Messages
6,014,218
Members
441,808
Latest member
xplainer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back