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

VoG · Feb 26, 2011

Maybe like this

Code:

Sub AddRow()
Dim LR As Long
LR = Range("A" & Rows, Count).End(xlUp).Row
Rows(LR).Copy
Rows(LR + 1).Insert
End Sub

Legacy 173501 · Feb 27, 2011

Thanks VoG, 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.excelforum.com/excel-wor...tom-of-a-table-using-a-macro.html#post2480264

Marcol · Feb 27, 2011

Using your posted macro as a guide to your requirements

Try this

Code:

Option Explicit
Sub AddRowWithFormulae(rng As Range, AddRows As Long)
    Range(Cells(rng.Row, rng.Column), Cells(rng.Row + AddRows, rng.Column + rng.Columns.Count - 1)).FillDown
End Sub
Sub AddRows()
    Dim LastRow As Long
 
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("O" & LastRow & ", M" & LastRow & ", K" & LastRow & ", I" & LastRow & ", G" & LastRow & ", E" & LastRow & ", C" & LastRow).ClearContents
 
    AddRowWithFormulae Range("B" & LastRow & ":P" & LastRow), 1
 
End Sub

Change the last arguement in this

Code:

AddRowWithFormulae Range("B" & LastRow & ":P" & LastRow), 1

to suit the number of rows you need to add.

Please fix your post in ExcelForum as requested and reply accordingly
http://www.excelforum.com/excel-wor...w-to-the-bottom-of-a-table-using-a-macro.html

Legacy 173501 · Mar 9, 2011

Hi Marcol,

Sorry for taking so long to respond, and thanks for your suggestion.

However, when I run this Macro, I receive an error that reads "Invalid Inside Proceedure".

Thanks again,

Milky

Marcol · Mar 10, 2011

I can't replicate your problem, however I might have misread your macro

Try this

Code:

Option Explicit
Sub AddRowWithFormulae(rng As Range, AddRows As Long)
    Range(Cells(rng.Row, rng.Column), Cells(rng.Row + AddRows, rng.Column + rng.Columns.Count - 1)).FillDown
End Sub
Sub AddRows()
    Dim LastRow As Long
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    AddRowWithFormulae Range("B" & LastRow & ":P" & LastRow), 1
    
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("O" & LastRow & ", M" & LastRow & ", K" & LastRow & ", I" & LastRow & ", G" & LastRow & ", E" & LastRow & ", C" & LastRow).ClearContents
End Sub

Legacy 173501 · Mar 12, 2011

Hi Marcol,

Thanks again for your help with this.

The code you posted is very good, and I no longer receive an error message.

However, the new row that is added appears to be outside the scope of the table. This is somewhat problematic because the new row is not included in the filter or sort functions in the same way that the rows in the table are. Do you think it is possible to change the code so that the new row becomes part of the existing table?

Also, the data in column B is actually a date. When I manually create a new row by copying the data from the row above, this value increases by one, which is actually quite helpful. As such, would it be possible for the value in column B of the newly added row to increase by one each time? This isn't nearly as important as the above request, but it would be nice.
Once again, I’m very grateful for your efforts.

Best wishes,

Milky

Marcol · Mar 12, 2011

Try this

Code:

Option Explicit
 
Sub AddRowWithFormulae(rng As Range, AddRows As Long)
    Range(Cells(rng.Row, rng.Column), Cells(rng.Row + AddRows, rng.Column + rng.Columns.Count - 1)).FillDown
End Sub
 
Sub AddRows()
    Dim LastRow As Long
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    AddRowWithFormulae Range("B" & LastRow & ":P" & LastRow), 1
 
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("O" & LastRow & ", M" & LastRow & ", K" & LastRow & ", I" & LastRow & ", G" & LastRow & ", E" & LastRow & ", C" & LastRow).ClearContents
 
    With ActiveWorkbook.Names("Statistics")
        .Name = "Statistics"
        .RefersToR1C1 = "=Data!R6C2:R" & LastRow & "C16"
    End With
 
End Sub

My apologies, I missed that bit in your question.

As a matter of interest have you tried using Lists (in 2003 and earlier) or Tables (2007 onwards)

Seems to me that would do your task without the need for VBa

Cheers
Alistair

Legacy 173501 · Mar 12, 2011

Hi Marcol,

All of this data is part of a Table in Excel 2007, but I saw no obvious way of adding a row to the bottom of the Table which also copies the formulas in some of the cells of the row above - as your code does. If there is a way of doing so without VBA, please do educate me!

I have tried your updated code, but now receive an error: "Run-time error 1004: Application-defined or object-defined error".

If I click Debug, the following line of Code is highlighted:

Code:

With ActiveWorkbook.Names("Statistics")

When I close the debugger, a new row is added with the correct formulae (as your code did before), but it still appears to be outside the scope of the table.

Many thanks for your time,

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

Hope this helps

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

Legacy 173501

Guest

Excel Facts

VoG

Legend

Legacy 173501

Guest

Marcol

Well-known Member

Legacy 173501

Guest

Marcol

Well-known Member

Legacy 173501

Guest

Marcol

Well-known Member

Legacy 173501

Guest

Marcol

Well-known Member

Similar threads

Forum statistics

Share this page

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

Legacy 173501

Guest

Excel Facts

VoG

Legend

Legacy 173501

Guest

Marcol

Well-known Member

Legacy 173501

Guest

Marcol

Well-known Member

Legacy 173501

Guest

Marcol

Well-known Member

Legacy 173501

Guest

Marcol

Well-known 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