How do I transpose data in columns to rows?
12,257
We're gonna learn you some VBA this morning and you're going to feel good about that! Put on your rocket boots and get ready to sail!
This macro will do it for you if it's all in column A
Hit Alt + F11 to bring up VBA editor. Right click insert - module
. Paste the code below in the module and to go debug - compile project
and hit F5 to run it. Easy breezy.
Sub transposedelete()
Dim rownum As Long
Dim colnum As Long
Dim data, result
colnum = 1
Application.ScreenUpdating = False
'check if the data on the sheet start where the code expects it
If Range("a1") = "" Then Exit Sub
' define data range
With Range("a1", Cells(Rows.Count, "a").End(xlUp)).Resize(, 2)
data = .Value
numrows = UBound(data)
'loop it
For rownum = 2 To numrows
Range((Cells(rownum, colnum)), (Cells(rownum + 1, colnum))).Copy
' transpose
Cells(rownum - 1, colnum + 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
'delete rows (this could be cleaner)
Rows(rownum).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Rows(rownum).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Next
End With
Application.ScreenUpdating = True
End Sub
Easy as 1, 2, 3:
1.
2.
3.
See now that wasn't difficult or intimidating, was it? And now you have a trick up your sleeve! Learning is great fun!
Related videos on Youtube
Author by
J. McLain
Updated on September 18, 2022Comments
-
J. McLain over 1 year
How do I transpose data in columns to rows?
If the data is:
Joe some city some state Bob no city no state
and I use Paste Special » Transpose, I get
Joe some city some state Bob no city no state
What I want is:
Joe some city some state Bob no city no state
I do not know VBA.
-
Ignacio Vazquez-Abrams about 12 yearsThat is transposition.
-
Raystafarian about 12 years@Arkive it is nearly a dupe but since the data is positioned differently, I'm not sure about closing it.
-
-
mischab1 about 12 yearsThe article you link to is how to split one column to multiple columns, not rows.
-
octern about 12 yearsWhen you put multiple columns side-by-side, you've created rows. Depending on how the data in the single column are laid out, you might have to transpose after breaking into multiple columns.