vba - Excel Table - Add a Row Formulation - Stack Overflow

The table starts at B7 and ends at B5 on the left and on the right starts at AA7 and ends at AA26. I want the "add row" button to add a row with the same formulas as in the rest of the table. However, the very last row of the table is the sum total row. This row should stay in tact and the sum total formula should update as the table updates. Please help me to write a macro for this. I have tried everything, I can't get it right. Either the formulas are messed up or it messes up the sum total row or worse. I just want to add a row if I run out of rows, but I want it to be in the same format as the rest of the rows and not mess up the sum total row at the bottom. I want to be able to keep adding rows and for it to update dynamically. The sheet is just the current sheet it's on. Please see image attached.

This code below ALMOST works, but it adds a row second to last instead of last and it messes up the last formula. The last formula should multiply R x V instead of N x R.

Sub ClearContents()
'
' Macro1 Macro

    ActiveSheet.Range("G7:L27").ClearContents
    ActiveSheet.Range("N7:P27").ClearContents
    ActiveSheet.Range("R7:T27").ClearContents
    ActiveSheet.Range("V7:Y27").ClearContents
    
End Sub
Sub AddRow()
'
' Macro3 Macro

    Dim ws As Worksheet
    Dim lastFormattedRow As Long
    Dim newRow As Long
    Dim totalRow As Long
    Dim borderColumns As Variant
    Dim col As Variant

    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet

    ' Define key rows
    totalRow = 26
    lastFormattedRow = totalRow - 1
    newRow = totalRow

    ' Insert a new row for the new data
    ws.Rows(newRow).Insert Shift:=xlDown

    ' Copy formatting from the last formatted row to the new row
    ws.Rows(lastFormattedRow).Copy
    ws.Rows(newRow).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False

    ' Clear the contents of the new row
    ws.Rows(newRow).ClearContents

    ' Set the "total est. weight" column (Column AA) to the product of R and V
    With ws.Cells(newRow, ws.Columns("AA").Column)
        .FormulaR1C1 = "=RC[-13]*RC[-9]" ' Formula for R * V
    End With

    ' Adjust the borders of the previous last row
    borderColumns = Array("M", "Q", "U", "Z")

    For Each col In borderColumns
        ws.Cells(lastFormattedRow, ws.Columns(col).Column).Borders(xlEdgeBottom).LineStyle = xlNone
    Next col

    ' Move the total row (row 26) down by inserting a new row below it
    ws.Rows(totalRow + 1).Insert Shift:=xlDown
    ws.Rows(totalRow).Copy
    ws.Rows(totalRow + 1).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False

    ' Ensure formulas in the total row (e.g., AA:AE) remain intact
    Dim totalRange As Range
    Set totalRange = ws.Range("AA" & totalRow + 1 & ":AE" & totalRow + 1)
    totalRange.FormulaR1C1 = ws.Range("AA" & totalRow & ":AE" & totalRow).FormulaR1C1

    ' Update the "total est. weight" column in the total row
    With ws.Cells(totalRow + 1, ws.Columns("AA").Column)
        .Formula = "=SUM(AA7:AA" & totalRow & ")"
    End With
    
        ' Delete the row two rows before the totalRow
    ws.Rows(totalRow + 1).Delete

    MsgBox "Row added successfully!", vbInformation
End Sub

The table starts at B7 and ends at B5 on the left and on the right starts at AA7 and ends at AA26. I want the "add row" button to add a row with the same formulas as in the rest of the table. However, the very last row of the table is the sum total row. This row should stay in tact and the sum total formula should update as the table updates. Please help me to write a macro for this. I have tried everything, I can't get it right. Either the formulas are messed up or it messes up the sum total row or worse. I just want to add a row if I run out of rows, but I want it to be in the same format as the rest of the rows and not mess up the sum total row at the bottom. I want to be able to keep adding rows and for it to update dynamically. The sheet is just the current sheet it's on. Please see image attached.

This code below ALMOST works, but it adds a row second to last instead of last and it messes up the last formula. The last formula should multiply R x V instead of N x R.

Sub ClearContents()
'
' Macro1 Macro

    ActiveSheet.Range("G7:L27").ClearContents
    ActiveSheet.Range("N7:P27").ClearContents
    ActiveSheet.Range("R7:T27").ClearContents
    ActiveSheet.Range("V7:Y27").ClearContents
    
End Sub
Sub AddRow()
'
' Macro3 Macro

    Dim ws As Worksheet
    Dim lastFormattedRow As Long
    Dim newRow As Long
    Dim totalRow As Long
    Dim borderColumns As Variant
    Dim col As Variant

    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet

    ' Define key rows
    totalRow = 26
    lastFormattedRow = totalRow - 1
    newRow = totalRow

    ' Insert a new row for the new data
    ws.Rows(newRow).Insert Shift:=xlDown

    ' Copy formatting from the last formatted row to the new row
    ws.Rows(lastFormattedRow).Copy
    ws.Rows(newRow).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False

    ' Clear the contents of the new row
    ws.Rows(newRow).ClearContents

    ' Set the "total est. weight" column (Column AA) to the product of R and V
    With ws.Cells(newRow, ws.Columns("AA").Column)
        .FormulaR1C1 = "=RC[-13]*RC[-9]" ' Formula for R * V
    End With

    ' Adjust the borders of the previous last row
    borderColumns = Array("M", "Q", "U", "Z")

    For Each col In borderColumns
        ws.Cells(lastFormattedRow, ws.Columns(col).Column).Borders(xlEdgeBottom).LineStyle = xlNone
    Next col

    ' Move the total row (row 26) down by inserting a new row below it
    ws.Rows(totalRow + 1).Insert Shift:=xlDown
    ws.Rows(totalRow).Copy
    ws.Rows(totalRow + 1).PasteSpecial Paste:=xlPasteAll
    Application.CutCopyMode = False

    ' Ensure formulas in the total row (e.g., AA:AE) remain intact
    Dim totalRange As Range
    Set totalRange = ws.Range("AA" & totalRow + 1 & ":AE" & totalRow + 1)
    totalRange.FormulaR1C1 = ws.Range("AA" & totalRow & ":AE" & totalRow).FormulaR1C1

    ' Update the "total est. weight" column in the total row
    With ws.Cells(totalRow + 1, ws.Columns("AA").Column)
        .Formula = "=SUM(AA7:AA" & totalRow & ")"
    End With
    
        ' Delete the row two rows before the totalRow
    ws.Rows(totalRow + 1).Delete

    MsgBox "Row added successfully!", vbInformation
End Sub

• excel
• vba
• insert

Share Improve this question Follow edited Jan 24 at 23:38 braX 11.8k55 gold badges2222 silver badges3737 bronze badges asked Jan 22 at 22:48 matt m matt m 1111 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Instead of using complex VBA code, I suggest defining your table as a ListObject (Insert -> Table). This allows you to easily manipulate the table, including adding new rows effortlessly.

Sub AddBlankRow()
    Dim tbl As ListObject
    Dim ws As Worksheet
    Dim lastRow As ListRow
    
    ' Set the worksheet and table
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    Set tbl = ws.ListObjects("Table1") ' Change "Table1" to your table name
    
    ' Add a new row at the end of the table
    Set lastRow = tbl.ListRows.Add
    
End Sub