Help and Support

Error message when you try to hide columns in Excel: "Cannot shift objects off sheet"

Article ID:211769
Last Review:June 7, 2007
Revision:9.2
This article was previously published under Q211769
For a Microsoft Excel 97 version of this article, see 170081 (http://support.microsoft.com/kb/170081/).

For a Microsoft Excel 98 Macintosh Edition version of this article, see 178959 (http://support.microsoft.com/kb/178959/).
On This Page

SYMPTOMS

If you try to hide columns of data in Microsoft Excel, you may receive the following error message:
Cannot shift objects off sheet.
To determine the unique number that is associated with the message that you receive, press CTRL+SHIFT+I. The following number appears in the lower-right corner of this message:
100185

Back to the top

CAUSE

This error message occurs if both of the following conditions are true:
You create an object such as a cell comment in any cell in a column.
You try to hide the column to the left of the column that contains the object, the column that contains the object, and all the columns to the right of the column that contain the object.
For example, this problem occurs if you put a cell comment in cell IR1, and then try to hide columns IQ:IS (at the same time).

Note This problem occurs with most objects that can be inserted into a worksheet, including but not limited to cell comments, graphs, drawing shapes, and pictures. Pay special notice to cell comments. By default and unlike most objects, cell comments are hidden and may not be immediately visible.

Note also that depending on the location of the column that contains the object, you may receive the error message if you try to hide the column that has the object and all the columns to the right of the column that has the object.

Back to the top

RESOLUTION

To resolve this problem, use one of the following methods.

Back to the top

Method 1: Change the position property of the object to "Move and size with cells"

1.If the object is a cell comment, select the cell that contains the comment. Right-click the cell, and then click Show Comment or Show/Hide Comments. This makes the comment visible.
2.Move the pointer to the edge of the object until the pointer turns into a white arrow pointer with four small black arrows on the pointer. Click the object to select it.
3.In Microsoft Office Excel 2003 and in earlier version of Excel, click <object name> on the Format menu. In this menu command, <object name> is the name of the object, such as "Comment" or "AutoShape".

In Microsoft Office Excel 2007, click Format in the Cells group on the Home tab. Then, click Format <object name>.
4. In the Format dialog box, click the Properties tab.
5.Click Move and size with cells, and then click OK.
6.If you want to hide the cell comment again, right-click the cell, and then click Hide Comment.
Perform these steps for each object in the affected column as described in the "Cause" section. When you hide the columns, you do not receive the error message.

Back to the top

Method 2: Change the property on all the objects on the active worksheet

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
Note Running the following macro sets the property that is mentioned in Method 1 for all the objects on the active worksheet. Because this setting causes objects to resize when the rows and the columns that are associated with the object are resized, it can cause unexpected results when it displays the objects on the worksheet if you resize the rows and the columns. Consider this problem before you run the macro in your file.

To change the property on all the comments on the active worksheet, run the following macro.
Sub Test()
Dim s As Shape 
On Error Resume Next
For Each s In ActiveSheet.Shapes 
s.Placement = xlMoveAndSize
Next 
End Sub

Back to the top


APPLIES TO
Microsoft Office Excel 2007
Microsoft Office Excel 2003
Microsoft Excel 2002 Standard Edition
Microsoft Excel 2000 Standard Edition

Back to the top

Keywords: 
kberrmsg kbpending kbprb KB211769

Back to the top

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.