Adding a row to the bottom of a table using a macro?

Legacy 173501 · Feb 26, 2011

Hi guys,

Is it possible to add a row to the bottom of a table using a macro?

Ideally, the new row would also copy the formulas in the cells of the row above, from which it was copied.

I've tried recording a macro to do this, but it always inserts a row below a given row - the row that was the final row when I recorded the macro - and not the final row at the time that the button is pressed!

For example, in a table with three rows (1, 2 and 3), I would like to click a button to add a row 4 to the table. I would also like the formulas in row 3 to be copied to row 4. I would then like to click the same button to add row 5 to the table, which would have its formulas copied from row 4 (etc.).
Can this be done?

I hope it makes some sense!

Many thanks in advance,

Milky

Marcol · Mar 12, 2011

It works okay with me.
Is "Statistics" the Name of your table? ie Your named range.
If not change the code to suit your name.

See this link on how to use Tables
http://office.microsoft.com/en-us/e...nd-columns-in-an-excel-table-HA010013765.aspx

[EDIT]
Browser told me the previous post was a broken link (Server not found) and I sent this again, hence the duplicate post.
Can this be deleted?

Marcol · Mar 12, 2011

To get the Date to increment it would be easiest to put your start date in B6
Then
In B7
=B6+1
Drag this down to the end of the table
Then the Date will increment as the table grows with the existing code.

If this is not practical then add this to the end of the Sub AddRows

Code:

Range("B" & LastRow) = Range("B" & LastRow - 1) + 1

Legacy 173501 · Mar 13, 2011

Hi Marcol,

Thanks again for all your help with this.

In the end, I managed to record what I wanted using a healthy combination of reading the document you pointed me toward and clicking the "Relative References Macro".

Best wishes,

Matt

quepasajohn · Jul 11, 2019

ActiveSheet.ListObjects("Table1").ListRows.Add

Adding a row to the bottom of a table using a macro?

Legacy 173501

Guest

Marcol

Well-known Member

Excel Facts

Marcol

Well-known Member

Legacy 173501

Guest

quepasajohn

New Member

Similar threads

Forum statistics

Share this page

Adding a row to the bottom of a table using a macro?

Legacy 173501

Guest

Marcol

Well-known Member

Excel Facts

Marcol

Well-known Member

Legacy 173501

Guest

quepasajohn

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock