Transpose Data

[Blessy Clara]

Hi,

Could someone let me know how to transpose Rows to columns using VBA.

Paste Special "Transpose" in excel may not be helpful for the type of data i have.

I have the details of 100 people in A Column

1.Sam Meyer
Unilever R&D
Colworth, Sharnbrook
Bedford MK44 1LQ
UK

2.George Geffrey
IRIT-UT1
Université Toulouse 1 Sciences Sociales
2, rue du Doyen Gabriel Marty
31 042 Toulouse Cedex 9
France

3.Lambert Lucy
GUESS
Dept. Informática, c6.3.18
Faculdade de Ciências da Universidade de Lisboa
Campo Grande
1749-016 Lisboa
Portugal

Hint -
Each individual's detail is separated with a blank row (could this used as the criteria to transpose!!!)

Also please note - the number of rows may vary (for example Lambert Lucy has details in 7 rows and George Geffrey in 5 rows)

Desired output to be like

Iqbal Adjali	Unilever R&D Colworth, Sharnbrook 	Bedford MK44 1LQ	UK

Ie - Each Individual's data in rows to be transposed to cloumns

I hope it is understandable

Thank you

 

[VoG]

Try this with a copy of your sheet

Sub Addrs()
Dim Area As Range
For Each Area In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Area(1).Offset(, 1).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
Columns("A").Delete
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Blessy Clara]

Hi VoG.

Thank you very much for the help. Yeah, that is excellent.