Recently, while working on a small worksheet in Excel 2007, I tried to insert a new row. Up popped an annoying error message, “Cannot shift objects off sheet.” I couldn't insert a new row, and had to drag things around, to get the new data inserted.
Keep reading, to see how I investigated the problem, and fixed it with a simple keyboard shortcut.
Change the Object Properties
In older versions of Excel, this problem usually occurred if you tried to insert columns and there was a comment hidden in one of the columns to the right. To fix the problem, you could change the properties of the comment box, so it would move with the cells.
View a List of Objects
It was odd – as far as I knew, the worksheet didn’t have any objects or comments, and only a few rows. What was causing the problem? Maybe there were some hidden shapes that I’d added, and forgotten about, or a comment tucked in a dark corner.
To see what's on a sheet in Excel 2007, you can use the Selection and Visibility Pane.
- Click the Home tab on the Ribbon.
- At the far right, click Find & Select, then click Selection Pane.
It lists all the visible and hidden shapes on the worksheet, including comments, and a data validation dropdown. Visible objects have an eye icon at the right, and hidden objects have an empty icon.
In this example, there’s a comment, data validation drop down, and a Bevel shape on the worksheet.
A Fix For Excel 2007
However, in my problem workbook the Selection Pane list was empty. The old Properties fix wouldn’t work, because there were no objects to change. Fortunately, a search of MSKB turned up an article with the solution:
http://support.microsoft.com/kb/211769
Method 1 solved the problem for me – in Excel Options, change the workbook Display setting for objects, from Nothing (hide objects) to All.
The keyboard shortcut to toggle this setting is Ctrl + 6 – much quicker than opening the Excel Options window.
Problem solved, and I’ve had no more problems inserting rows.
Working with Shapes and Objects
For more information on working with Excel Shapes, and some of the problems you can encounter, visit the Delete or Hide Objects/Controls on a worksheet page on Ron de Bruin’s web site.
________________________
Solved the message during Auto-filtering simply by using the Comment finder (Review tab/Comments section/Next button)in version 2007, and deleting 4 Comments.
Something to do with Comments apparently conflicts with hidden rows.
Worked perfectly afterwards.
Regards
Greg, Thank you very much. I tried everything and only your advice helped me. Thanks a lot. I am so happy now!!!
Thanks Greg, this worked for me!
I was going crazy figuring it out!!!!
Thank you for solving my problem.
control + 6 . It solved the problem immeidately.
thanks
Thank you Yashdeep from Texas.
I will try CNTL + 6 next time this happens.
This sounds must faster than finding and deleting old Comments in filtered rows.
Greg
Ctrl 6 worked for me. Thx alot.
I wonder what causes this to happen. Because i didn't change the settings and it just happen, then i suddenly need to change the settings to fix it up. I guess this is kinda sort of a virus or something. The solution works for me though.
wow...amazing...i was going crazy with this problem and you solved it in a second..thank you thank you thank you
ctrl + 6 solved cannot shift object off sheet
Thank you.
ctrl 6 worked like a charm
Choosing "All" has no effect on my Excel 2007. Nor does Ctrl + 6 (or F6 or any other 6 I can find). What am I missing?
I came accross the issue initially when trying to use the filter to group rows of data.
I tried the ctrl+6 or choosing "all" but it didn't work for me but when I changed all the comments properties to "move and size with cells" it solved the issue.
Applying the fix to all the comments was a bit tedious so it would be nice if MS put something in the Excel Options pages to allow this to be instantly applied to all comments in the workbook/sheet.
Thanks very much for taking the time to put this info out there though - saved me a load of stress.
It was the comments. Thanks Greg!
The control 6 worked for me! Thanks so much.
Thank you so much! Ctrl 6 also worked for me :)
Ctrl 6 erased all my graphics. I solved the problem by removing the hyperlinks from all my pics (right click on graphic and select remove hyperlink). I saved the spreadsheet and exited Excel. I then reopened the spreadsheet and I could insert all the rows I wanted. I have Excel 2010.
@Judy, the Ctrl + 6 shortcut will hide or show all the objects in a workbook. I hope you were able to get the graphics back by pressing Ctrl + 6 again.
Thanks for sharing how you fixed the problem in your workbook.
When I pressed Ctrl 6 the graphics did not come back. Luckily I saved before I tried it! Always a good plan when you try something new and you are very welcome!
Great! your last hint Debra worked for me!
Thanks
Thanks so much! Finally I could solve this problem.
This one worked for me>>
Method 1 solved the problem for me – in Excel Options, change the workbook Display setting for objects, from Nothing (hide objects) to All.
Thank u so much greg .
THANKS SO MUCH. I SOLVE MY PROB. BY USING CTRL+6
Thanks!
I had a grouped set of columns with comments and I inserted a group of 4 colums. I took out the comments and no more error.
Thanks...
Cannot Shift Objects Off Sheet in Excel 2007
By Rithy Than 06 Oct, 2012
Recently, while working on worksheet in Excel 2007, I tried to hide row. Up popped an annoying error message, “Cannot shift objects off sheet.” I couldn't hide the row.
Please reply me soon,
Thank you anyway.
similar situtation with "Cannot shift objects off sheet” message when filtering with hidden columns, this occured after using a file for 14 months with no problems.
Control 6 did not fix the problem long term. Deleteing recently added comments resolved the issue. Thanks doug.
My excel version is excel 2003.
When I delete all of the comments from each cell then this problem is solved.
Thanks everybody.
Thanks a lot!!! It works, I deleted all comments and now my excel is really fine.
:)