My formulas won't update when I insert a row - Help!

Bakerson

New Member
Joined
May 30, 2008
Messages
4
I have a spreadsheet for recording various financial data, at the bottom of which are subtotals [subtotal(9,range)] of some but not all columns.
I've got the following Code to insert new rows, copying formulas from a hidden row at the top of the spreadsheet.

A = Int(InputBox("No of New rows?", "DATA ENTRY"))
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Rows(LastRow).Resize(A).Insert Shift:=xlDown
Rows(1).Copy Range("A" & LastRow).Resize(A)
Rows(LastRow).Resize(A).Hidden = False

This successfully inserts new rows with formulas.
The problem I have is that my subtotals don't update to include my newly inserted row(s)

Does anyone have any ideas how to overcome this?
Help gratefully received.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Here's one suggestion:

1. Add a blank row immediately above your subtotal row.

2. Alter your Subtotal formula to include that new blank row in its 'range'.

3. Hide the blank row

4. Alter this line in your code
Rich (BB code):
LastRow = Range("A" & Rows.Count).End(xlUp).Row - 1
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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
Back
Top