Delete row but leave formula intact in Col H

I have the following formula in Col H:

HTML Code: 

It is part of a bank register and each cell in Col H is filled with
the same formula, adjusted for each row.

Right now if the user needs to delete the entry (row)
the cell with the formula complains with #REF.

How can we delete a row and keep the formula intact in Col H?

Originally Posted by MarvinP

Perhaps

=IF(A$42=0,"",SUM(H$41,G$42-E$42))

I'd need to see what row was being deleted to see if the above works.

Thanks Marvin. No that wouldn't work. Here are a few more rows so you see the pattern...

HTML Code: 

I have discovered an ugly procedure that works. I could write VBA code to automate this.
Is there an easier way?

I would like to be able to delete the row(s) the cursor is in.
So if the cursor is highlighting three rows, then delete those three rows.

Here's my procedure (I discovered that by copying, I bypass formula cell errors in Col H)

1. Select any cell(s) in row(s) to delete
2. Select Col A:G and delete cell(s)
3. Select all rows below (Col A:G) and Copy
4. Paste to row(s) above
5. Select last row(s), Col A:G and delete

Originally Posted by Journeyman3000

You're explicitly referring to a cell that won't be there after you've deleted it. It will not update because it is not on the row your function is on.

Instead, use the Offset function

Please Login or Register  to view this content.

This way you can reference the cell in the H column in which the function resides, and offset to the row above it, rather than explicitly stating the cell. whenever you delete the row above, the offset fucntion will still survive, and refer to the row above it.

Easy mode.

Thanks very much for your help!
I can see how this will allow me to delete rows and keep the formula intact.

There's just one problem I'm having.

The spreadsheet will be used on two computers.
One using Excel 2002 and one using Excel 2013.
Most of the time it will be Excel 2002.

What is currently happening is when I delete a row on the Excel 2002
computer, everything freezes.
The only way out is to click the 'x' in the top right corner and choose
'Cancel'.

On the Excel 2013 computer everything works fine. The row is deleted and
there is no freezing.

I just did a test where I deleted all of the VBA code for the spreadsheet
and the freezing stopped after deleting a row.
So it has something to do with the VBA code.

Can someone possibly tell me what the offending code is and how I can fix it?

Originally Posted by bakerman2

Put this line on the top row of the code

Please Login or Register  to view this content.

and try again.

Thanks for helping out bakerman.

I tried your suggestion but it is still freezing after the row is deleted.

Originally Posted by MarvinP

Hi Chris,

Perhaps your On_Change macro is changing something, which changes something, recurse... hang.

Just after the On_Change is fired (and works) you might want to turn off other events with a

Please Login or Register  to view this content.

Turn them back on (True) at the end of that code.

Thanks.
I just tried this change:

Please Login or Register  to view this content.

But it didn't help.

Withdrawn-LLN

Originally Posted by bakerman2

I told you to put it on top so like this.

Please Login or Register  to view this content.

Wow! That was great.
Finally it is working as it should.
I can delete rows now and preserve the formulae in Col H.

Thank you very much!