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

**MilkyQuail** · 02-26-2011, 02:55 PM

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

**pike** · 02-26-2011, 11:48 PM

Hi MilkyQuail
if you need the last row to replace in your macro

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

**MilkyQuail** · 02-27-2011, 08:50 AM

Thanks Pike, but that doesn't seem to work - or perhaps I'm not using it correctly?

Perhaps it would help if I told you some more about my setup.

I have a table named “Statistics” on a Sheet named “Data”, which covers the area B6:P37.
I want the macro to add a row to the bottom of that table, and copy the formulas from the row above into the new bottom row. Right now, my macro reads like this:

Sub AddDate()
'
' AddDate Macro
'

'
    Selection.End(xlDown).Select
    ActiveWindow.SmallScroll Down:=6 [these two rows seems to select a cell in the last row of the table]
    Range("B37:P37").Select  [This is the selection of the last row – as you can see, it only selects some fixed cells, rather than all of the cells between columns B and P on the last row]
    Selection.AutoFill Destination:=Range("B37:P38"), Type:=xlFillDefault [This copies down the formulas from the entire row above to the new final row]
    Range("B37:P38").Select
    Range("O38").Select
    Selection.ClearContents [This is just because I’d like to clear the contents of some of the copied cells, however, again, it selects fixed cells rather than “column O on the last row”)
    Range("M38").Select
    Selection.ClearContents
    Range("K38").Select
    Selection.ClearContents
    Range("I38").Select
    Selection.ClearContents
    Range("G38").Select
    Selection.ClearContents
    Range("E38").Select
    Selection.ClearContents
    Range("C38").Select
    Selection.ClearContents
End Sub

I hope this is useful!

Best wishes,

Milky

Edit - Crosspost - http://www.mrexcel.com/forum/showthr...27#post2627627

**royUK** · 02-27-2011, 08:53 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**MilkyQuail** · 03-09-2011, 04:25 PM

All,

Apologies, I have now edited my post as requested.

Thanks again for your efforts,

Milky

**Marcol** · 03-09-2011, 07:40 PM

Did you check your thread on MrExcel?

**royUK** · 03-13-2011, 05:11 AM

Which version of Excel are you using. In most versions you don't need a macro to do this, just inbuilt Excel features.

1) In most versions of Excel before 2007 - Tools -> Options - > Edit - > make sure "Extend Data Range Formats & Formulas" is selected.

2) Excel 2003, use the List feature

3) Excel 2007 the List feature was improved & became Tables

There's no point resorting to macros when Excel can do what you want automatically