Help creating a macro to name ranges

[ba8910]

I need a macro to name the ranges, name should be the table heading.

Here is the example

<table class="cms_table" width="192"><tbody><tr class="cms_table_tr" valign="top"><td class="cms_table_td" width="64">Table 1</td> <td class="cms_table_td" width="64">
</td> <td class="cms_table_td" width="64">
</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Name</td> <td class="cms_table_xl65">Age</td> <td class="cms_table_xl65">Sales</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Prakash</td> <td class="cms_table_xl65">25</td> <td class="cms_table_xl65">20000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Rani</td> <td class="cms_table_xl65">30</td> <td class="cms_table_xl65">30000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">
</td> <td class="cms_table_xl65">
</td> <td class="cms_table_xl65">
</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Table 2</td> <td class="cms_table_xl65">
</td> <td class="cms_table_xl65">
</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Name</td> <td class="cms_table_xl65">Age</td> <td class="cms_table_xl65">Sales</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Prakash</td> <td class="cms_table_xl65">25</td> <td class="cms_table_xl65">20000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Rani</td> <td class="cms_table_xl65">30</td> <td class="cms_table_xl65">30000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Prakash</td> <td class="cms_table_xl65">25</td> <td class="cms_table_xl65">20000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Rani</td> <td class="cms_table_xl65">30</td> <td class="cms_table_xl65">30000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Rani</td> <td class="cms_table_xl65">30</td> <td class="cms_table_xl65">30000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Prakash</td> <td class="cms_table_xl65">25</td> <td class="cms_table_xl65">20000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Rani</td> <td class="cms_table_xl65">30</td> <td class="cms_table_xl65">30000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">
</td> <td class="cms_table_xl65">
</td> <td class="cms_table_xl65">
</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Table 3</td> <td class="cms_table_xl65">
</td> <td class="cms_table_xl65">
</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Name</td> <td class="cms_table_xl65">Age</td> <td class="cms_table_xl65">Sales</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Prakash</td> <td class="cms_table_xl65">25</td> <td class="cms_table_xl65">20000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Rani</td> <td class="cms_table_xl65">30</td> <td class="cms_table_xl65">30000</td> </tr> <tr class="cms_table_tr" valign="top"><td class="cms_table_td">Prakash</td> <td class="cms_table_xl65">25</td> <td class="cms_table_xl65">20000</td> </tr> </tbody></table>



1st name of the range should be "Table1" next "Table2" and so on... it is possible that there are many more columns.

 

[wigi]

Cross-posted at Ozgrid:

http://www.ozgrid.com/forum/showthread.php?t=159117

 

[wigi]

Sub naming_ranges()

    Dim rng As Range

    For Each rng In ActiveSheet.UsedRange.Columns(2).SpecialCells(2, 2).Areas
        With rng.CurrentRegion
            .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).Name = Replace(.Cells(1).Text, " ", "")
        End With
    Next

End Sub