• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cannot Insert Rows / How to Insert Rows So That Formulas Copy

S

Susan J

New Member
1) I need to insert rows from the blue highlighted line down but, I keep getting the error message that you will get also. I have gone to CtrlEnd many times but it does not go ahead.

2) I need to know how to insert a line and have the previous lines' formula's copy as well. Also, is there a way to copy formula's so that the #DIV0! does not show or at lease shows as something else?


http://www.2shared.com/file/9HmHxqFf/CVR-Test_2012_12_08.html
 
Hi Susan ,


I tried inserting rows below row 9 and row 19 , without any problems ; can you mention the error that you are getting ? Also , specify the row number where the cursor is to be placed , and row insertion tried.


Regarding your second question , your formula at present is :


=IF(B25=1,H25,(H25/B25))


This is somewhat redundant ; if B25 equals 1 , then the result of the calculation H25/B25 will be H25 ; your formula should be taking care of the situation where B25 = 0 , since in this case , the calculation H25/B25 will generate the #DIV/0! error.


Rewrite your formula as :


=IF(B25=0,0,H25/B25)


Narayan
 
Thank you so much Narayank991. I continue to learn and so appreciate your help. It makes total sense and you are correct, all of the DIV0!'s are now gone.


I can insert one line, then I get the following error message:

'To prevent possible loss of data, Excel cannot shift nonblank cells off of the workshet. Select another location in which to insert new cells, or delete data from the end of your worksheet.


If you do not have data in cells that can be shifted off of the worksheet, you can reset which cells Excel considers nonblank. To do this, press CTRL+End to locate that last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save."


Earlier in the evening, I hide all of the rows past a certain line number and then this started happening. Make sense?
 
OK, to clarify my insert line problem, I am trying to insert a line in Line 9. I am simply selecting the line, right clicking and selecting Insert. One line inserts but, then I get the above error message for the next insert.

I hope that helps.
 
Hi Susan ,


Now everything is clear !


In any worksheet , if you press the CTRL END keys together , you will see the cursor placed on the last used cell in the worksheet ; do this in yours , and see where it takes you.


What you need to do is delete all the unused rows , and save your worksheet ; if even this does not work , you can go into the VB editor and reset the UsedRange property. See if the deletion action works. If not , please let us know.


You can also select the actual range of data in your worksheet , and copy it to a new workbook , and save that as your actual file.


The following link has a lot of information on this :


http://www.ozgrid.com/forum/showthread.php?t=16410


Narayan
 
Thanks for the detailed response, in my case, I had to go into the VB editor and reset the UsedRange property. I appreciate your help.
 
You must log in or register to reply here.
Back
Top