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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,446
Office Version
365
Platform
Windows
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
 

Bakerson

New Member
Joined
May 30, 2008
Messages
4
Lovely jubbly - that works a treat!
I had tried offset(-1), but it really didn't like that!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,939
Messages
5,447,410
Members
405,451
Latest member
BalbasNiBarabas

This Week's Hot Topics

Top