Increasing excel table databodyrange

[Costas]

Hi,

I want to increase my excel table range by an extra column. I found this code but when I try it, it doesn't seem to work.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count, tbl.Columns.Count+1).Select

I know that I need to have the active cell in the table but it still doesn't work. Also not sure why I need to use offset here.

 

[NARAYANK991]

Hi Costas ,

The following works :

Public Sub Resize_Table()
          Dim tr As Range
          Dim lo As ListObject
        
          Set lo = ActiveSheet.ListObjects(1)
          Set tr = lo.Range
          lo.Resize tr.Resize(, tr.Columns.Count + 1)
End Sub

This assumes you have only one table on your activesheet.

Narayan

 

[NARAYANK991]

Hi Costas ,

If you wish to retain your code , change it slightly as follows :

Public Sub Resize_Table()
          Dim tbl As ListObject
          Set tbl = ActiveCell.CurrentRegion.ListObject
          tbl.Resize tbl.Range.Resize(, tbl.Range.Columns.Count + 1)
End Sub

Narayan

 

[Costas]

Thank you very much for your replies. I tested them and they work fine. I then tried to rename my new column and it automatically creates a second new one with "MMM-YY" name. Below is the code that I've used to rename.

    X = MyObject.ListColumns.Count
    Range("D11").Offset(0, X).Value = "MMM-YY"

Shoud I comment out the first new column code and just keep the above or would there be cases that it won't work. Also, instead of Range("D11") is there another way of selecting the top left cell of my table?

Thanks
Costas

 

[NARAYANK991]

Hi Costas ,

Try this :

Public Sub Resize_Table()
          Dim MyObject As ListObject
          Set MyObject = ActiveCell.CurrentRegion.ListObject
          X = MyObject.ListColumns.Count
          MyObject.HeaderRowRange.Cells(1, X + 1).Value = "MMM-YY"
End Sub

Narayan

 

[Costas]

Many thanks Narayan. It did the trick!