# Delete row but leave formula intact in Col H

1. ## Delete row but leave formula intact in Col H

I have the following formula in Col H:

HTML Code:
``=IF(A42=0,"",SUM(H41,G42-E42))``
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?

2. ## Re: Delete row but leave formula intact in Col H

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.

3. ## Re: Delete row but leave 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:
``````=IF(A42=0,"",SUM(H41,G42-E42))
=IF(A43=0,"",SUM(H42,G43-E43))
=IF(A44=0,"",SUM(H43,G44-E44))
=IF(A45=0,"",SUM(H44,G45-E45))
=IF(A46=0,"",SUM(H45,G46-E46))``````

4. ## Re: Delete row but leave formula intact in Col H

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

5. ## Re: Delete row but leave formula intact in Col H

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.

``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.

6. ## Re: Delete row but leave formula intact in Col H

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.

``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?

7. ## Re: Delete row but leave formula intact in Col H

Most likely it's this code in the ThisWorkBook section that's giving you trouble.
``Please Login or Register  to view this content.``
Put this line on the top row of the code
``Please Login or Register  to view this content.``
and try again.

8. ## Re: Delete row but leave formula intact in Col H

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.

9. ## Re: Delete row but leave formula intact in Col H

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.

10. ## Re: Delete row but leave formula intact in Col H

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.

11. ## Re: Delete row but leave formula intact in Col H

In an effort to troubleshoot this I have removed most of the VBA code and
am planning to put back parts one at a time to see the results.

With most removed, I was able to delete a row and not have the spreadsheet freeze.
But then when I deleted another row something strange happened.

I have a conditional formatting set up so that the color of rows alternates:
blue/white/blue/white etc. for Col A:H.

When I just deleted another row (it worked) the formatting for all the rows
got screwed up!

Now instead of having all Col A:H for the rows alternating in color,
all columns except Col H changed to white!

Only Col H continued to alternate in color.
Is that weird?

I continued deleting rows...
There are now 45 rows in the sheet.
I deleted a few rows from the top. OK.
Then I deleted row 11 and check this out...

Row 29, Col A,B,D,E,G changed to white (rest stayed blue)!
Row 31, Col A,B,D,E changed to white (rest stayed blue)!

Checking into this..
The Conditional formatting formula for the above rows changed
(on their own) from:

=MOD(ROW(),2)=1 to

=A\$1=TODAY()

in all the misbehaving cells mentioned above!

Besides finally getting the delete figured out (without freezing) I have a feeling that
I won't be able to delete rows without messing up the cond. formatting of alternating colors
for rows.

12. ## Re: Delete row but leave formula intact in Col H

Withdrawn-LLN

13. ## Re: Delete row but leave formula intact in Col H

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

``Please Login or Register  to view this content.``

14. ## Re: Delete row but leave formula intact in Col H

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!

15. ## Re: Delete row but leave formula intact in Col H

You're welcome and thanks for rep+.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1