Transpose dynamically

rasmus79

Board Regular
Joined
Sep 4, 2003
Messages
66
I am hoping to "recreate" the transposing of text and numbers that I can do with "COPY" - PASTE SPECIAL (And then selecting transpose)
IE. I have lots of cells with data horizontally on ROW1, but I would like a copy of them along collumn A (Starting on ex A3)
Does anyone know how to do this?
Thanks
I tried combining a sum and index and match, but that didnt work for text. I just want to return what is in the cell. No summing or multiplying or anything like that.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
I am sure there is a better way to do this, but here is one solution.

This will take the entire row from B to IV for the current active cell and tranpose it into Column A.
Code:
Sub trans()
Dim r As Long
Dim col As Long
Dim content

r = ActiveCell.Row
For col = 2 To 256
content = Cells(r, col).Value
Cells(col, 1) = content
Next
End Sub
 

rasmus79

Board Regular
Joined
Sep 4, 2003
Messages
66
Thanks - I have to get better at using macros. I do not feel too secure on how to write those.
Does anyone have a good way of doing this just through formulas?
 

rasmus79

Board Regular
Joined
Sep 4, 2003
Messages
66
Fairwinds! You did it again.
I tried to use the formula you helped me with earlier, but I got stuck on the MATCH function rather than index.
Thanks again!
 

howzat

Board Regular
Joined
Oct 13, 2003
Messages
144
Just for interest sakes, there is the = transpose() formula.

This needs to be array entered.
 

Forum statistics

Threads
1,147,847
Messages
5,743,522
Members
423,801
Latest member
paulj4177

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

Which adblocker are you using?

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
Top