From clumns to rows

[Juuljus]

Hello everyone,

I have a table. There are headers and below them is data. 3 rows and 10
columns. I need to get this table to change it's format.

from:
a1 b1 c1 d1
a2 b2 c2 d2
a3 b3 c3 d3

to:
a1 a2 a3
b1 b2 b3
c1 c2 c3
d1 d2 d3

Thanks to everyone.

Juuljus

 

[Bob Phillips]

Select the cells, then Copy

Select first target cell, Edit>PasteSpecial and select Transpose

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[princeofchaos]

There are several ways to do so.
The first one:
1 - Copy the cells.
2 - Select the first cell where you want to copy to.
3 - Next to the paste icon on the toolbar, there is an arrow. Press it
and select Transpose.

the second one:
1 - Select the area where you want to copy the data.
2 - Write the following: =TRANSPOSE([in here put the are of the data
you cpy from]), and press Ctrl+Shift+Enter.

 

[Guest]

Juuljus,
Select your range and do "Edit-->Copy" or right-click,
Copy). Select cell where you want to paste and highlight three (3) cells
across and ten (10) down. Right click, Paste Special and Transpose.

Code below will do the same

HTH

Sub ColumnsTtoRows()

Dim r As Long, c As Integer
Dim lastrow As Long, lastcol As Integer
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
With ws1
r = 2
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
lastcol = .Cells(2, Columns.Count).End(xlToLeft).Column
For c = 1 To lastcol
.Range(.Cells(2, c), Cells(lastrow, c)).Copy
ws2.Cells(r, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
r = r + 1
Next c
End With
End Sub

 

[Juuljus]

Big thanks to Bob and princeof...