How can I transpose multiple rows into one column.

**Doodie** · 12-15-2012, 04:26 PM

Hi there, I wish to transpose multiple lines into one column, I have attached a file for an example. Thank you for your help.

**xladept** · 12-15-2012, 05:07 PM

Try this:

Sub RowstoColumn(): Dim Ro As Range, r As Long: r = 7
SetRo: Set Ro = Range("F" & r & ":J" & r): Ro.Copy
Range("A" & r).PasteSpecial Transpose:=True
r = r + 5
If Range("F" & r) = "" Then Exit Sub
GoTo SetRo: End Sub

**Doodie** · 12-15-2012, 05:26 PM

Thank you, it works. I need to learn VBA, it would save so much time.

**xladept** · 12-15-2012, 06:49 PM

You're welcome! - Yeah, I really like VBA and Excel.

**Ghozi Alkatiri** · 12-15-2012, 07:20 PM

write at A7 :
=TRANSPOSE(E7:I7)
then select range A7:A11 (5 rows)
push Ctrl +shift +enter (array formula)
copy result down

**mike7952** · 12-16-2012, 08:47 AM

Another

Sub abc()
 Dim i As Long
 
 For i = 7 To Cells(Rows.Count, "a").End(xlUp).Row Step 5
    Cells(i, "e").Resize(, 5) = _
    Application.Transpose(Cells(i, "a").Resize(5))
 Next
 
End Sub