Add a Row under a table when the table expands.

[randall78]

HI,
I need some help with VBA. I have a number of tables separated by a blank ROW.

Capture.PNG

As data is added to the tables they expend down to fill the empty row. When this happens i need a new row to be inserted under that table allowing the table to expand again the next time i add more data to that table.

[Roel Jongman]

The default way table expand works is that the blankrow is not filled but the other tables and data is pushed down
That is if the picture shows true excel tables and not just formatted cells.
You do not need VBA for that to happen

[randall78]

Thank you for the reply, however when a table expands it fill the blank row and doesn't push the other tables down.

[Roel Jongman]

Post an example of your file (an excel file not a picture) then because by default it does. It is impossible to guess from a picture why it is not working for you

[randall78]

Sorry for the delay had PC issues. Here is an example. Try adding a date in the first table and you will see that the table expands as it should but a new row is not inserted underneath.

[Roel Jongman]

YesI see what you mean, while I was answering I was thinking about what the trick or the setting was. as I remembered later having problems with it in the past.
There is no setting for this to force this behavior. You need a little excel-magic to make it work I have applied it in your example.

To keep a blank row between your table you have to put some text in the cell below the table* and then make the text the same color as the spacing row (most likely white)
When there is data below a table the table expand function will push that data down when you insert a new row.

Have a look at the attachment I created 2 extra tables that will auto expand and push down other tables as well.
A small trick instead of a setting


*if the table auto expands on you simple press CTRL+Z or undo button once to undo the expand, or use the resize grip of the table

[randall78]

Thanks again for your help however with regards to your example, yes the table does expand to include any new data, but a new Row between the tables is still not being inserted.