Why does "cannot shift objects off sheet" prevent row insertion?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am unable to insert rows in an existing spreadheet and a new one I just
initiated in a book with multiple sheets. What did I do to cause this and
how do I make it go away?
 
If there is something in row 65535 you can not insert a row as that would
expect the data to move to row 65536 which doen't exist in Excel (except
2007, I believe). Hit Ctrl & End to move to the last used row/column and see
what's there.

Ian
 
hi
excel is confused. it's internal reference to used space is off. not to
worry. it happen now and then.
to correct.... select all unused columns to the right of your data and delete
select all unused rows below your data and delete. that should reset
everything.

Regards
FSt1
 
After following FSt1's advice..............save the workbook for the reset to
take place.


Gord Dibben MS Excel MVP
 
Thank you. I did that and have no change. It still says cannot shift items
off sheet. This is excel 07 if that makes a difference.
 
There is nothing at the end.

Ian said:
If there is something in row 65535 you can not insert a row as that would
expect the data to move to row 65536 which doen't exist in Excel (except
2007, I believe). Hit Ctrl & End to move to the last used row/column and see
what's there.

Ian
Click to expand...
 
hi again,
the advice i gave along with gord's advice(which i forgot) always worked for
me but in 2003 back. I don't have 2007, haven't worked with it so i don't
know. You may need to seek out a 2007 expert on this one.

sorry
FSt1
 
Thank you I will keep it posted

FSt1 said:
hi again,
the advice i gave along with gord's advice(which i forgot) always worked for
me but in 2003 back. I don't have 2007, haven't worked with it so i don't
know. You may need to seek out a 2007 expert on this one.

sorry
FSt1
Click to expand...
 
Thank you. There are no comments in the sheet, no merged or hidden cells
either. Pre 07 auditing checked for phantom sheet errors (though I don't
remember exactly how to get there) I do not seem to find that option in 07.
I have checked for links, charts, etc. none exist.
 
Could you have hidden shapes in that worksheet?

Maybe you could run a macro to unhide those shapes:

Option Explicit
Sub testme()
Dim iCtr As Long
For iCtr = 1 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(iCtr).Visible = True
Next iCtr
End Sub

Then use
F5 (ctrl-g)|Special|Objects
and hit the delete key
to remove ALL the shapes.

Don't do this if there are shapes that should remain hidden or shapes that
should not be deleted.
 
Be sure to "Delete Selected Columns" and "Delete Selected Rows". Simply
clearing cell contents with the Delete key will *not* do what you need. Then
save workbook & reopen.
 
Has anyone been able to solve this for you?
I ask only because I have the same problem in Excel 2007 and none of the
solutions here solves the problem.
This happens on all the sheets in my workbook and I know I do not have any
hidden data.
 
Hello, I too would like an answer to this one or at least a way to get around
it. Can you fool the program into letting you add columns? Any help here
would be appreciated. I upgraded from an older version and none of the noted
solutions are working. Thanks. Kelly
 
Kelly said:
Hello, I too would like an answer to this one or at least a way to get around
it. Can you fool the program into letting you add columns? Any help here
would be appreciated. I upgraded from an older version and none of the noted
solutions are working. Thanks. Kelly
Click to expand...

Excel apparently thinks you have data in the last row of your
spreadsheet. To check this, type Ctrl-End, which should take you to the
last entry of your workbook.
One approach is to delete all the rows after where you have placed data.
A second approach is to highlight all your data, then copy and paste to
another sheet or another workbook.
Sorry if these solutions have already been mentioned in other replies.
My Thunderbird newsreader only shows this one response and not the full
thread.

Bill
 
I have also had this problem and need to resort to a "work around". However,
when I copy the data in the sheet to another workbook, I lose all my named
ranges referred to in the associated macros. Any ideas?
 
Thanks for the reply, Bob. However, the document you referenced was
mentioned earlier in the thread. None of the solutions worked as Kelly Girl
and eperry also posted. What I really need is a work around. The problem
disappears if I copy my range of used cells to another workbook, but then I
lose all my named ranges (about 50) and would like to know if my only option
is to recreate all the named ranges in the new workbook. Maybe I'll just
bite the bullet and recreate them all.
Thanks, again,
Kim
 
Sorry, I wasn't seeing the whole thread the latest message juts popped up in
my reader.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top