Cannot shift objects off sheet Excel 2010

[LauraM]

I am trying to hide all the columns from E to the end of the sheet but keep getting the error message cannot shift objects off sheet.

Everyone says this is down to hidden objects in the sheet or hidden comments but I have used the clear all icon to remove everything from the cells that I want to hide but still keep getting the error message.

I have checked the find and select selection pane and there is only one comment on the sheet and that is in cell C3 so nothing to do with that.

I haven't copy and pasted anything into the sheet so its not images as someone else suggested.

Please can anyone help I am beginning to think my Excel is broken.

Laura

[Simon H]

This error can be due to objects in the cells you are trying to manipulate but it can also be due to an object overlapping from a nearby row or column as in the image below. The comment is in Cell C3 but the hover over pop up window overlaps onto columns D, E & F. You need to leave enough unhidden space on the spreadsheet to the right of column C otherwise you will get the message cannot shift objects off sheet.

You can edit the comment and drag the edit box to a new position where it doesn’t overlap the columns that you want to hide, as in the image below. This will allow you to hide all the columns that you want to.

The problem with this is that, although you can reposition the comment window whist editing, the hover over pop up window will still be anchored in the same place when you mouse over it and consequently with the columns hidden it won’t show properly as in the image below. Unfortunately there is no way to change the default anchor point of the hover over pop up window.

You could chose to have the comment showing all the time, in which case it will be displayed in the same position as the edit box as in the image below. This could look messy though if you had lots of comments on the sheet

Another workaround would be to drag the unhidden column so it’s wide enough to display the pop up window correctly as in the image below. This way you can have the comment hidden until its needed.

The other thing you could do is replace the comment with a data validation message as in the image below.The problem with this would be that the cell needs to be selected for the message to show so you will need to choose what suits your worksheet best.

[LauraM]

This error can be due to objects in the cells you are trying to manipulate but it can also be due to an object overlapping from a nearby row or column as in the image below. The comment is in Cell C3 but the hover over pop up window overlaps onto columns D, E & F. You need to leave enough unhidden space on the spreadsheet to the right of column C otherwise you will get the message cannot shift objects off sheet.

You can edit the comment and drag the edit box to a new position where it doesn’t overlap the columns that you want to hide, as in the image below. This will allow you to hide all the columns that you want to.

 

The problem with this is that, although you can reposition the comment window whist editing, the hover over pop up window will still be anchored in the same place when you mouse over it and consequently with the columns hidden it won’t show properly as in the image below. Unfortunately there is no way to change the default anchor point of the hover over pop up window.

You could chose to have the comment showing all the time, in which case it will be displayed in the same position as the edit box as in the image below. This could look messy though if you had lots of comments on the sheet

Another workaround would be to drag the unhidden column so it’s wide enough to display the pop up window correctly as in the image below. This way you can have the comment hidden until its needed.

The other thing you could do is replace the comment with a data validation message as in the image below.The problem with this would be that the cell needs to be selected for the message to show so you will need to choose what suits your worksheet best.

 

Cannot shift objects off sheet is now solved by making the column wider.