Results 1 to 6 of 6

Thread: Getting Error cannot shift nonblank cells off the worksheet in Microsoft Excel

  1. #1
    Join Date
    Jun 2011
    Posts
    26

    Getting Error cannot shift nonblank cells off the worksheet in Microsoft Excel

    I "inherited" an Excel template at work, author unknown (v2003 SP3). I get a sporadic error message when I try to insert a row or copy/insert a row. Suggested fix most often does not work. Sometimes it resolves by itself. I don't know what I am doing to cause the error, nor how to fix it when it happens. I am stuck and need help from someone familiar with this issue. Thanks. Error message: "To prevent possible loss of data, Microsoft Office Excel cannot shift nonblank cells off the worksheet. Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save yourworkbook to reset the last cell used. Or, you can move the data to a new location and try again."

  2. #2
    Join Date
    Nov 2009
    Posts
    675

    Re: Getting Error cannot shift nonblank cells off the worksheet in Microsoft Excel

    From the error message: "Try to locate the last nonblank cell by pressing CTRL+END, and delete or clear all in cells between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used. Or, you can move the data to a new location and try again."

  3. #3
    Join Date
    Apr 2010
    Posts
    37

    Re: Getting Error cannot shift nonblank cells off the worksheet in Microsoft Excel

    Read the below link. It provides a bit detail on the error and a fix for the same. You can try searching information on officehelp also.
    Cannot Shift Nonblank Cells Off Sheet - http://support.microsoft.com/kb/69491

  4. #4
    Join Date
    Feb 2010
    Posts
    589

    Re: Getting Error cannot shift nonblank cells off the worksheet in Microsoft Excel

    You want to insert a column (or row) and Excel tells you. "To avoid the possible loss of data Microsoft Excel can not shift nonblank cells off the worksheet. " The board seems appropriate. One possible reason might be the formatting (eg a border) added to an entire column or row. The solution for is that you will need to remove the formatting of the cells to the right or bottom of your table by CTRL + SHIFT RIGHT arrow (to select all columns to the right of the column where you are) and choose: FORMAT or ERASE ALL CLEAR according to your needs and your problem, and everything should return to normal.

  5. #5
    Join Date
    Feb 2010
    Posts
    678

    Re: Getting Error cannot shift nonblank cells off the worksheet in Microsoft Excel

    Another possibility is that you have something in the last column (Column IV) sheet or the last row (row 65536) and for this reason when you try add one more, Excel is not capable of displacing the text. To fix this, stand on the last columns of the worksheet, select it and checks if there is any text, if you see nothing, however, remove them (this if you must allow the), do likewise with the latest rows.

  6. #6
    Join Date
    Jun 2012
    Posts
    1

    Re: Getting Error cannot shift nonblank cells off the worksheet in Microsoft Excel

    Update on Limits:
    -------------------------------------
    Microsoft Excel 2003 : 65,536 rows by 256 columns
    Microsoft Excel 2007 & Microsoft Excel 2010 : 1,048,576 rows by 16,384 columns


    Also in Microsoft Excel 2010, try this:
    -------------------------------------
    File > Check for Issues > Check Compatibility

    :)

Similar Threads

  1. How to renumber cells in Microsoft Excel
    By Weslee in forum MS Office Support
    Replies: 2
    Last Post: 24-01-2012, 04:25 PM
  2. Replies: 4
    Last Post: 11-01-2012, 03:56 PM
  3. Replies: 8
    Last Post: 04-12-2011, 10:24 AM
  4. How to copy worksheet in Microsoft Excel 2010?
    By Bharat89 in forum Windows Software
    Replies: 3
    Last Post: 09-01-2010, 03:09 PM
  5. Excel error "Cannot shift objects off worksheet"
    By Caiden in forum Windows Software
    Replies: 3
    Last Post: 29-08-2009, 07:49 PM

Posting Permissions

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