Transposing a list

[mlz]

I have a list I would like to transpose. Below is an example.

<table x:str="" style="border-collapse: collapse; width: 48pt;" border="0" cellpadding="0" cellspacing="0" width="64"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">name</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">address</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">tel</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">email</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="name " height="17">name </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">address</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">name</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="address " height="17">address </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">tel</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="name " height="17">name </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">tel</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="email " height="17">email </td> </tr> </tbody></table>
This is what I would like the macro to do.

<table x:str="" style="border-collapse: collapse; width: 192pt;" border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">name</td> <td style="width: 48pt;" width="64">address</td> <td style="width: 48pt;" width="64">tel</td> <td style="width: 48pt;" width="64">email</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="name " height="17">name </td> <td>address</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">name</td> <td x:str="address ">address </td> <td>tel</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:str="name " height="17">name </td> <td>tel</td> <td x:str="email ">email </td> <td>
</td> </tr> </tbody></table>
Currently I am copying and transposing. I tried recording a macro but I have not had luck. In between every contact I have a blank space.
Thanks,
MLZ

 

[Domski]

Do you have anything that identifies the entry as to what it is, i.e. does it actually say name, address, tel or e-mail before the entry or is that going to have to be detected by the macro?

Dom

 

[mlz]

For each contact only the cell with the telephone and email identify the content of the cell.

Thanks,
MLZ

 

[Domski]

So are they shown like:

Tel: 07851541564
E-mail: bob.hope@hotmail.com

?

Dom

 

[facethegod]

You can try

Option Explicit
Sub tst()
Dim r As Range, a, i As Integer
Set r = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
Set r = r.SpecialCells(xlCellTypeConstants)
For i = 1 To r.Areas.Count
a = Split(Join(Application.Transpose(r.Areas(i)), ","), ",")
Cells(i, 2).Resize(1, UBound(a) + 1) = a
Next
End Sub

HTH

 

[mlz]

This is what it <table x:str="" style="border-collapse: collapse; width: 267px; height: 108px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 171pt;" width="228"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 171pt;" height="17" width="228">


PH: 555-555-5555
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">FX: 555-555-5555
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">EM: bob.hope@hotmail.com</td> </tr> </tbody></table>