changing dimensions of a table with macro

[aminexcel]

Hi All Members
this is my table, [200 countries in columns and more than one million codes in rows]

<table style="border-collapse: collapse; width: 227pt;" border="0" cellpadding="0" cellspacing="0" width="302"><col style="width: 41pt;" width="55"> <col style="width: 62pt;" width="83"> <col style="width: 68pt;" width="90"> <col style="width: 56pt;" width="74"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl63" style="height: 14.25pt; width: 41pt;" align="center" height="19" width="55">CODE</td> <td class="xl64" style="border-left: medium none; width: 62pt;" align="center" width="83">ABW</td> <td class="xl64" style="border-left: medium none; width: 68pt;" align="center" width="90">AFG</td> <td class="xl64" style="border-left: medium none; width: 56pt;" align="center" width="74">ALB</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010190</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">1,212,439,339</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">719,840,574</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">18,891,976</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010210</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">490,700,000</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">221,219,716</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">28,238,532</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl65" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010290</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">1,861,732,713</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">485,934,833</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">178,300,478</td> </tr> </tbody></table>
i want to change it like this by a macro

<table style="border-collapse: collapse; width: 132pt;" border="0" cellpadding="0" cellspacing="0" width="177"><col style="width: 41pt;" width="55"> <col style="width: 29pt;" width="39"> <col style="width: 62pt;" width="83"> <tbody><tr style="height: 14.25pt;" height="19"> <td class="xl65" style="height: 14.25pt; width: 41pt;" align="center" height="19" width="55">CODE</td> <td class="xl65" style="border-left: medium none; width: 29pt;" align="center" width="39">ISO</td> <td class="xl65" style="border-left: medium none; width: 62pt;" align="center" width="83">DOLLAR</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010190</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">ABW</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">1,212,439,339</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010190</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">AFG</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">719,840,574</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010190</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">ALB</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">18,891,976</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010210</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">ABW</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">490,700,000</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010210</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">AFG</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">221,219,716</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010210</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">ALB</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">28,238,532</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010290</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">ABW</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">1,861,732,713</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010290</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">AFG</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">485,934,833</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">010290</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">ALB</td> <td class="xl68" style="border-top: medium none; border-left: medium none;" align="center">178,300,478</td> </tr> </tbody></table>
any idea

 

[Special-K99]

How many columns does this table have?

 

[aminexcel]

number of columns differ in different computations. if it is possible, i want a macro that work for any number of columns and rows

sincerely

 

[Norie]

Do you actually mean you want to transpose the data?

Let's say the original data is on a worksheet called 'Data' and there is a worksheet called 'Result' in the same workbook, try this code.

Option Explicit
Sub TransposeData()
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim rngDst As Range
Dim rngSrc As Range
Dim NoCols As Long
 
    Set wsDst = Worksheets("Result")
    Set wsSrc = Worksheets("Data")
 
    Set rngDst = wsDst.Range("A2")
 
    rngDst.Resize(, 3).Offset(-1) = Array("CODE", "ISO", "DOLLAR")
 
    Set rngSrc = wsSrc.Range("A2")
 
    NoCols = rngSrc.End(xlToRight).Column - 1
 
    While rngSrc.Value <> ""
 
        rngSrc.Copy rngDst.Resize(NoCols)
 
        rngSrc.Offset(-rngSrc.Row + 1, 1).Resize(, NoCols).Copy
        rngDst.Offset(, 1).PasteSpecial Transpose:=True
 
        rngSrc.Offset(, 1).Resize(, NoCols).Copy
        rngDst.Offset(, 2).PasteSpecial Transpose:=True
 
        Set rngDst = rngDst.Offset(NoCols)
 
        Set rngSrc = rngSrc.Offset(1)
 
    Wend
 
    Application.CutCopyMode = False
 
End Sub

This should work for any no of rows and if the no of columns is the same for each row.

If the no columns differ then the code will need to be changed to take account of that.

 

[aminexcel]

Dear Norie
thanks a lot, it solved my problem


cheers