Transpose Columns to Rows by blank

[snehaa]

<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>

Andrea Anderson	85.98
	97.62
	100.00
	100.00
Jane Austin	89.81
	78.70
	100.00
Jack Willow	94.32
Anna Kendrick
Frida Kahlo	90.91
	0.00
	88.54
	76.96
	94.32
	89.11

<tbody>
</tbody>

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>

Andrea Anderson	85.98	97.62	100.00	100.00
Jane Austin	89.81	78.70	100.00
Jack Willow	94.32
Anna Kendrick
Frida Kahlo	90.91	0.00	88.54	76.96	94.32	89.11

<tbody>
</tbody>

I am using the following 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?

 

[Mike Szczesny]

Can you use something like this? Assume your for Person A is 1,2,3, and 4 in B1:B4. Assume you will place formula in D1. Since there are 4 numbers, you will need to use your mouse to select all the cells D1:G1. Enter the formula =transpose (D1:G1). You need to press Cntrl+Shift+Enter at the same time to enter this. Then you are done. This formula produces horizontal answers for a vertical range and vice versa

a	1		a	1	2	3	4
	2
	3
	4
b	5
	6
	7
c	8
	9
	10
	11

<colgroup><col span="8"></colgroup><tbody>
</tbody>