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

#### eazyyexcel

##### New Member
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
144.1 KB · Views: 8
• excel 2-min.jpg
143.7 KB · Views: 8

#### LazyBug

##### Board Regular
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.

Replies
12
Views
124
Replies
0
Views
111
Replies
5
Views
108
Replies
3
Views
102
Replies
5
Views
365

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.

### Which adblocker are you using?

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

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