Excel - Excel 2007 "Can not shift objects off sheet"

Asked By devistate on 24-Oct-08 12:45 PM
In prior versions of Excel, I had no problem inserting a row between two
other rows in a spreadsheet. However, every time in Excel 2007 that I try to
do this same operation that has worked fine in all prior versions of Excel, I
am prevented from doing the insert with a message "Can not shift objects off
sheet". The sheet is nowhere near full, so that inserting a new row between
two other rows will lose no information whatsoever. So Excel 2007 is not

How do I insert a row between two other rows without getting this message?

(Copying all the subsequent rows and then pasting them one row down is NOT
an acceptable answer. This is a workaround, not a solution.)




devistate replied on 24-Oct-08 12:51 PM
I should also have mentioned in my original post that I have tried deleting
all rows in the spreadsheet after the last row, so that I am absolutely
certain that there is no "hidden" information. This does not resolve the
problem.

Excel 2007 is doing something that no prior version of Excel has done, and
it is the source of this problem and nothing in my spreadsheets, which worked
fine under all prior versions of Excel.
ShaneDevenshir replied on 24-Oct-08 04:25 PM
Hi,

And when you say you deleted the rows you mean you selected the rown numbers
from say 50 down to 1,048,576 and press Ctrl+- (control minus), not the Del
key?

If you did that, try moving to a new blank worksheet and inserting a row at
row 1.  If it works you know that there is a problem on the other sheet.  It
may be a hidden object, such as a chart, shape, textbox, comments..., you can
try to find them by choosing Office Button, Excel Options, Advanced and
making sure that
For cells with comments, show is set to something other than No comments or
indicators.
And that For objects, show is set to All.
Then return to the spreadsheet and choose Home, Find & Select, Selection
Pane, and see what you find.

Alternative #2, copy everything from your current sheet to a new sheet.
Select on the rows where you think there is stuff.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire
devistate replied on 24-Oct-08 05:18 PM
Before I go into the following effort that is specific to one file, I want to
make clear that this is happening for every single pre-2007 spreadsheet that
I have and is not in any way unique to any particular pre-2007 spreadsheet.

I tried three different forms of deleting rows 43 to 1,048,576: DEL,
ATL+E+D, CTRL+- and while all of them worked, none of them resolved the row
insertion problem.

I did copy the rows to a new worksheet, and insertion does work there okay.

I tried your first alternative, and the options were already set the way you
said to set them. The SELECTION PANE was grayed out and unaccessible within
FIND & SELECT.

While your alternative #2 works, laboriously copying every single worksheet
of every single spreadsheet that I have is a workaround and not a solution.

The problem is that Excel 2007 is fundamentally treating spreadsheets
differently than all prior versions of Excel. Somehow it is seeing something
existing where no other versions of Excel did. Whatever it is seeing, it is
there in the very simplest of spreadsheets -- those in which it is nothing
but a single worksheet that has never had any objects of any kind in it.
Jim Rech replied on 25-Oct-08 12:19 PM
I haven't run into this problem but I don't use Excel 2007 all that much
yet.  So I opened several Excel 2003 workbooks in Excel 2007.  They opened
in compatibility mode of course. I inserted rows in maybe 8 worksheets in
total and didn't have a problem.  I would love to know what object Excel is
perceiving but I can't figure it out if I can't duplicate the problem.  If
you'd like to send me a workbook that exhibits the issue I'd be happy to see
if I can figure it out.  jrrech@hotmail.com

--
Jim