Resize table

[2022]

I have a table in a tab called Sheet2 that looks as follows:

The text 'ID' in cell G2, the word 'Food' in H2.

Then the numbers 1 and 2 in G3 and G4, respectively.

And the words 'Chocolate' and 'Tomato' in H3 and H4, respectively.

When I created the table the range of cells in it, if you go to the 'Name Manager' are G3:H4 in Sheet2

And I would like to resize the table using VBA by extending the bottom of it to cover the number of rows added to in column G.

So if someone added a new value in cell G5 (eg the number 3, which would then mean you have 1. 2 and 3 in cells G2:G5), then code would increase the table size so that it would now cover cells G3:H5 instead of G3:H4.

Does anyone know how I can modify the code so that it expands the number of rows the table covers, based in new row entries in column G (using cell references eg "G1", instead of the exisiting cell notation)?

It currently gives an error when it gets to the line that starts "Set Table = ThisWorkbooks.Sheets("Sheet2").ListObjects("Table1")" (even though the table is called Table1).

Sub resizetable()


Sheet2.Activate


Dim LastRow As Long

Dim Table As ListObject


LastRow = Cells(Rows.Count, "G").End(xlUp).Row


Set Table = ThisWorkbooks.Sheets("Sheet2").ListObjects("Table1")


Table.resize Range(Cells(3, 7), Cells(LastRow, 8))


End Sub

ID	Food
1​	Chocolate
2​	Tomato

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.