Incrementing SUM function to include newly inserted row in VBA

0
votes

I am trying to find a way to update the subtotal at the bottom of the table so it includes newly inserted row values.

For example, in the following spreadsheet, I have a button that inserts rows at the end of a table (to add new items). This button utilizes vba. I just inserted row 4 using vba, and it is blank. However, in the subtotal line, the sum function did not change to include the new row. What can I add to the end of my VBA code to modify that sum formula to include the new row (and extend the range down by one cell)? So that whenever I use my button to add a row, it also extended the range by one cell down? (to include b4)

Example Spreadsheet

1
excelvbasum
Please post your code. – tigeravatar
Why not sum up to the row just before the subtotal row? – AAA

1 Answers

1
votes

This code puts a formula in B20 based on the number of cells filled above it:

Sub Formulamaker()
    Dim rng As Range

    Set rng = Range(Cells(1, "B"), Cells(1, "B").End(xlDown))
    Range("B20").Formula = "=SUM(" & rng.Address & ")"
End Sub

In your example the formula is inserted two cells below the data. If you always want the formula to be placed two cells below the data then:

Sub Formulamaker()
    Dim rng As Range

    Set rng = Range(Cells(1, "B"), Cells(1, "B").End(xlDown))
    Cells(rng.Rows.Count + rng.Row + 1, "B").Formula = "=SUM(" & rng.Address & ")"
End Sub