Adding new row and automatically calculating the total amount at the bottom

eazyyexcel

New Member
Joined
Mar 1, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hello there,

Is there a way I could add a new row at the bottom of the table (just before the total sum), key in the new data and the final total will be automatically calculated? Currently I need to add a new row manually, then I copy and paste the previous row and I paste in the new row, delete all the data, and key in the amount. But I notice the total sum at the bottom is not being automatically calculated. I need to reformat the formula to add the calculations of the new row.

Would really appreciate anyone's help on this.

Thank you
 

Attachments

  • excel 1-min.jpg
    excel 1-min.jpg
    144.1 KB · Views: 8
  • excel 2-min.jpg
    excel 2-min.jpg
    143.7 KB · Views: 8

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
159
Office Version
  1. 2010
Platform
  1. Windows
Hi I have attached my file.
Hello! Maybe "one-click" VBA solution will suit you, in that case try this macro on a copy of your data. If i understand correctly, data for sum begins in the row 4. Macro inserts a new row to key in a new data at the bottom of table with SUM formulas amending in the D, F, H, I, J columns.
VBA Code:
Sub ezxl()
Dim lr&, arc(), i&
Application.Calculation = -4135

arc = Array(4, 6, 8, 9, 10)
    With ActiveSheet
        lr = Cells(4, 4).End(4).Row
        Rows(lr).Insert
        For i = LBound(arc) To UBound(arc)
            Cells(lr + 1, arc(i)).FormulaLocal = "=SUM(" & Range(Cells(4, arc(i)), Cells(lr, arc(i))).Address & ")"
        Next i
        Cells(lr, 4).Select
    End With
   
Application.Calculation = -4105
End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,130,123
Messages
5,640,245
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top