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

 

[kevin9999]

ThisWorkbooks.Sheets

Lose the "s"

ThisWorkbook.Sheets

 

[2022]

ThisWorkbooks.Sheets

Lose the "s"

ThisWorkbook.Sheets

Good spot!

I’ll try that later!

Thank you!

 

[Peter_SSs]

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.

In what circumstances does the table not automatically expand when values are added in column G?

 

[kevin9999]

I'm just wondering whether it's a true table, or simply a range 'named' Table1. When you select a cell within the table, say G3, do you get the menu Table Design appearing as a new menu option?

 

[2022]

In what circumstances does the table not automatically expand when values are added in column G?

The first circumstance is if you add a column of data to precede the first column of the table. So the table is G3:H4.

But what if I want to add data in cells F2:F4 (a header in F2 and numbers in F3 and F4, respectively) - how can I resize the table using VBA, to prevent having to resize it manually?

And the second circumstance is decreasing the table size.

So if the table goes from G3:H4 (with G2:H2 as headers) but the latest set of data that goes into the table only goes up to row 3 instead of row 4, how can I resize the table so that it shrinks the table so that the table only has the headers and one row of input data or it covers G3:H3 instead of G3:H4?

I tried this, but the line before 'End Sub' failed.....do you know why, please?

Sub resizetable()


Sheet2.Activate


Dim LastRow As Long

Dim Table As ListObject


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


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

'Cells(3,7) means row 3 and column G (G is the 7th column)

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

End Sub

 

[2022]

I'm just wondering whether it's a true table, or simply a range 'named' Table1. When you select a cell within the table, say G3, do you get the menu Table Design appearing as a new menu option?

Yes.

I think that the addition of the data described in my latest reply above is what may have caused some confusion.....

Please let me know if you want me to clarify further?

 

[Peter_SSs]

The first circumstance is if you add a column of data to precede the first column of the table. So the table is G3:H4.

And the second circumstance is decreasing the table size.

Neither of those possibilities seem to be covered by your initial stated requirements ..

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.

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

.. which specifically stated (twice) adding rows and only based on what was added in column G, so perhaps you can see why I asked the question.

Unless it is resolved beforehand, I will take another look at your revised question when I get a chance.

 

[Alex Blakenburg]

Give this a try:

Table.Resize Table.Range.CurrentRegion

 

[2022]

Give this a try:

Table.Resize Table.Range.CurrentRegion

Ok, that works! Thanks