Error when inserting rows

[Tara]

I have a user that keeps getting the same error when she
tries to insert a row. The error occurs no matter which
way she inserts the rows (menu, right-click, etc.)

Here is the error:

To prevent possible loss of data, Microsoft Excel cannot
shift nonblank cells off the worksheet. Try to delete or
clear the cells to the right and below 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.

Funny thing is, she is not attempting to shift nonblank
cells anywhere. All she wants to do is insert a blank
row! And she is nowhere near the end of the worksheet--
she only has a couple hundred rows. She has be able to
insert blank rows as needed for several months; the first
time this error occurred was yesterday.

Any help you can provide is appreciated!

 

[Paul]

I have a user that keeps getting the same error when she
tries to insert a row. The error occurs no matter which
way she inserts the rows (menu, right-click, etc.)

Here is the error:

To prevent possible loss of data, Microsoft Excel cannot
shift nonblank cells off the worksheet. Try to delete or
clear the cells to the right and below 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.

Funny thing is, she is not attempting to shift nonblank
cells anywhere. All she wants to do is insert a blank
row! And she is nowhere near the end of the worksheet--
she only has a couple hundred rows. She has be able to
insert blank rows as needed for several months; the first
time this error occurred was yesterday.

Any help you can provide is appreciated!

When you insert a row, row number 65536 is shifted off the worksheet. You
probably have something in a cell on row 65536 that you don't realise is
there - maybe even just a space character.

Select the row immediately below the area you are really using. Hold down
Ctrl and Shift and press the down arrow key; this will select all rows right
down to 65536. Then use Edit > Delete. (If you wish, you can repeat this for
columns to the right of the area you are using.) Save the file. You can
check that Ctrl+End now takes you just to the end of the area you are using.
Inserting rows will now work again as you expect. (You may also notice that
your file size has decreased dramatically.)

 

[Dave Peterson]

maybe this'll help:

XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns
http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769

Someone with the same problem posted back that their comments were getting
resized that was causing the trouble.

Maybe enabling: Tools|Options|View|Comment and indicator
(at least for a little bit will help.)

 

[tara]

That worked perfectly! Thanks!

-----Original Message-----


When you insert a row, row number 65536 is shifted off the worksheet. You
probably have something in a cell on row 65536 that you don't realise is
there - maybe even just a space character.

Select the row immediately below the area you are really using. Hold down
Ctrl and Shift and press the down arrow key; this will select all rows right
down to 65536. Then use Edit > Delete. (If you wish, you can repeat this for
columns to the right of the area you are using.) Save the file. You can
check that Ctrl+End now takes you just to the end of the area you are using.
Inserting rows will now work again as you expect. (You may also notice that
your file size has decreased dramatically.)


.