Nonblank Cells Error

[jac3130]

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.

 

[Jonmo1]

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

 

[ChrisR]

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

 

[jac3130]

Thanks for your help, that's it.

 

[Jonmo1]

Glad to help, thanks for the feedback.