+ Reply to Thread
Results 1 to 15 of 15

Delete row but leave formula intact in Col H

  1. #1
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002
    Posts
    147

    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. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2016
    Posts
    12,376

    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.
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002
    Posts
    147

    Re: Delete row but leave formula intact in Col H

    Quote Originally Posted by MarvinP View Post
    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. #4
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002
    Posts
    147

    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. #5
    Forum Contributor
    Join Date
    03-05-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    257

    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.

    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.
    Last edited by Journeyman3000; 05-19-2017 at 02:32 AM.

  6. #6
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002
    Posts
    147

    Re: Delete row but leave formula intact in Col H

    Quote Originally Posted by Journeyman3000 View Post
    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?
    Attached Files Attached Files

  7. #7
    Forum Expert bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    2,633

    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.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  8. #8
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002
    Posts
    147

    Re: Delete row but leave formula intact in Col H

    Quote Originally Posted by bakerman2 View Post

    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. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2016
    Posts
    12,376

    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. #10
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002
    Posts
    147

    Re: Delete row but leave formula intact in Col H

    Quote Originally Posted by MarvinP View Post
    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. #11
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002
    Posts
    147

    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.
    Last edited by ChrisXcel; 05-19-2017 at 11:56 PM. Reason: More weirdness

  12. #12
    Valued Forum Contributor
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    1,169

    Re: Delete row but leave formula intact in Col H

    Withdrawn-LLN

  13. #13
    Forum Expert bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    2,633

    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. #14
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2002
    Posts
    147

    Re: Delete row but leave formula intact in Col H

    Quote Originally Posted by bakerman2 View Post
    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. #15
    Forum Expert bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    2,633

    Re: Delete row but leave formula intact in Col H

    You're welcome and thanks for rep+.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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