<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px} span.Apple-tab-span {white-spacere} </style>I need to transpose vertical data from column B to horizontal data.
My table looks like this:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial; min-height: 11.0px}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}</style>
[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Andrea Anderson[/TD]
[TD="class: td1"]85.98[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]97.62[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]100.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]100.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jane Austin[/TD]
[TD="class: td1"]89.81[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]78.70[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]100.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jack Willow[/TD]
[TD="class: td1"]94.32[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Anna Kendrick[/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Frida Kahlo[/TD]
[TD="class: td1"]90.91[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]0.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]88.54[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]76.96[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]94.32[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]89.11[/TD]
[/TR]
</tbody>[/TABLE]
And I want it to look like this:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial; min-height: 11.0px}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #000000 #000000 #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}</style>
[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Andrea Anderson[/TD]
[TD="class: td1"]85.98[/TD]
[TD="class: td1"]97.62[/TD]
[TD="class: td1"]100.00[/TD]
[TD="class: td1"]100.00[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jane Austin[/TD]
[TD="class: td1"]89.81[/TD]
[TD="class: td1"]78.70[/TD]
[TD="class: td1"]100.00[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jack Willow[/TD]
[TD="class: td1"]94.32[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Anna Kendrick[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Frida Kahlo[/TD]
[TD="class: td1"]90.91[/TD]
[TD="class: td1"]0.00[/TD]
[TD="class: td1"]88.54[/TD]
[TD="class: td1"]76.96[/TD]
[TD="class: td1"]94.32[/TD]
[TD="class: td1"]89.11[/TD]
[/TR]
</tbody>[/TABLE]
I am using the following code:
The problem is .End(x1Down) doesn't work because there are single rows of data. Is there a solution for this?
My table looks like this:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial; min-height: 11.0px}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}</style>
[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Andrea Anderson[/TD]
[TD="class: td1"]85.98[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]97.62[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]100.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]100.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jane Austin[/TD]
[TD="class: td1"]89.81[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]78.70[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]100.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jack Willow[/TD]
[TD="class: td1"]94.32[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Anna Kendrick[/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Frida Kahlo[/TD]
[TD="class: td1"]90.91[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]0.00[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]88.54[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]76.96[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]94.32[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]89.11[/TD]
[/TR]
</tbody>[/TABLE]
And I want it to look like this:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Arial}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Arial; min-height: 11.0px}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 0.8px 0.8px 0.8px 0.8px; border-color: #000000 #000000 #000000 #000000; padding: 0.0px 5.0px 0.0px 5.0px}</style>
[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Andrea Anderson[/TD]
[TD="class: td1"]85.98[/TD]
[TD="class: td1"]97.62[/TD]
[TD="class: td1"]100.00[/TD]
[TD="class: td1"]100.00[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jane Austin[/TD]
[TD="class: td1"]89.81[/TD]
[TD="class: td1"]78.70[/TD]
[TD="class: td1"]100.00[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Jack Willow[/TD]
[TD="class: td1"]94.32[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Anna Kendrick[/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]Frida Kahlo[/TD]
[TD="class: td1"]90.91[/TD]
[TD="class: td1"]0.00[/TD]
[TD="class: td1"]88.54[/TD]
[TD="class: td1"]76.96[/TD]
[TD="class: td1"]94.32[/TD]
[TD="class: td1"]89.11[/TD]
[/TR]
</tbody>[/TABLE]
I am using the following code:
Code:
> Sub Transpose()
> Dim t As Range, u As Range
> c = ActiveCell.Column
> fr = ActiveCell.Row
> lr = Cells(Rows.Count, c).End(xlUp).Row
> r = fr
> Do
> Set t = Cells(r, c)
> Set u = t.End(xlDown)
> Range(t, u).Copy
> t.Offset(, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
> r = u.End(xlDown).Row
> Loop While r < lr
> Application.CutCopyMode = False End Sub
The problem is .End(x1Down) doesn't work because there are single rows of data. Is there a solution for this?