Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Nonblank Cells Error

  1. #1
    Board Regular
    Join Date
    Dec 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Nonblank Cells Error

    I'm currently receiving the following message:

    To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. 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 the 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.
    Job 1 is resolving the above but I'm also wondering: There also appears to be a lot of blank cells below my data. Is there anyway to make it so my scroll bar isn't so large on the right side?

    Thank you in advance.

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,052
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nonblank Cells Error

    The error pretty much means exactly what it says.
    You have Non Empty cells at the bottom of the sheet that can't be shifted off the sheet when you insert new rows.

    Even rows that 'appear' blank may not actually be blank.
    Have you applied formats to cells that are not actually in use?

    To resolve, highlight the row just below what you percieve as your last used row on the sheet.
    Press CTRL + SHIFT + DOWN (you may have to repeat this until it goes all the way to the bottom of the sheet)
    Right Click - Delete (important to do this, and not just press the Delete Key on your keyboard)
    Then Save / Close / Reopen the book
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular ChrisR's Avatar
    Join Date
    Dec 2012
    Location
    Birmingham, UK.
    Posts
    848
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nonblank Cells Error

    Hi,

    The issue will be that you need to delete rows and columns that you are not using but Excel is recognising that you are...........Press Ctrl+End and see where it takes you and then highlight the non-used rows between this and your data and right click and delete........Repeat the process for columns........

    Now when you save the worksheet it should now recognise the new 'end' of the data.

    Regards,
    Chris
    ďIíve seen how you canít learn anything when youíre trying to look like the smartest person in the room.Ē
    Barbara Kingsolver


  4. #4
    Board Regular
    Join Date
    Dec 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nonblank Cells Error

    Thanks for your help, that's it.
    Last edited by jac3130; May 29th, 2013 at 02:52 PM.

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,052
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nonblank Cells Error

    Glad to help, thanks for the feedback.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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