Contextures

Excel Error Cannot Shift Objects Off Sheet

If you try to hide or insert worksheet rows or columns, or if you try to apply a filter, you might see this Excel error: Cannot Shift Objects Off Sheet. In in some versions of Excel, you'll see this error: Can't push objects off the sheet. A simple shortcut fixed it for me: Ctrl + 6, and there are other suggestions below.

Excel Error Cannot Shift Objects Off Sheet

This short video shows the Excel error, Cannot Shift Objects Off Sheet, and how you can try to fix the problem. There are details and written steps below the video.

Video Timeline
  • 0:00 Introduction
  • 0:44 Open Excel Option Settings
  • 1:00 Advanced - Display Options Workbook
  • 1:17 For Objects, Show: All
  • 1:25 Test the Option Change
  • 1:38 Shortcut to Change Option

Error When Inserting Column

Recently, while working on a small worksheet in Excel, I tried to insert a new column. Instead of adding the columns, Excel showed an error message: Can't push objects off the sheet.

Some people see also this message when they try to apply a filter on a worksheet, or try to insert new rows.

Excel error: Can't push objects off the sheet

NOTE: In other versions of Excel, you might see this error message: Cannot shift objects off sheet.

Excel error: Cannot shift objects off sheet

How to Fix Shift Object Error

There are two solutions shown below, to fix the Cannot Shift Objects Error problem.

Fix 1: Change an Excel Option

  • Try this fix first - it's quick and easy

Fix 2: Change Comment Property

  • If the first fix doesn't work for you, try this one instead.

And if neither of those worked, there are a few more fixes that you can try

Fix 1: Change Excel Option

This fix is quick and easy, so try it first.

  • Go to the worksheet where you get the Excel error
  • Press this keyboard shortcut: Ctrl + 6
    • This shortcut turns a workbook display option setting on or off, to hide or show the objects
  • Repeat the steps that caused the error message, and see if the problem is fixed.

If you still see the error message, go to the next section, to try Fix 2

See the Excel Option

Instead of using the Ctrl + 6 shortcut, you can follow these steps to view or change the option setting manually:

  • On the Excel Ribbon, click File, then click Options (at the bottom left)
  • At the left, click Advanced
  • Scroll about halfway down, to the section: Display options for this workbook
  • Under "For objects, show:", select All
  • Click OK
Object display options for this workbook
Object display options for this workbook

Fix 2: Change Comment Property

If the first fix didn't solve the problem, try this comment property fix instead.

On your worksheet, the problem might be caused by:

  • hidden comments, or comments that are close to the end of the worksheet
  • those comments have a property that stops Excel from moving them

Follow the steps below to:

  1. Find all the comments on the worksheet
  2. Change a comment property, so Excel can move the comments, if necessary

Find All the Comments

If you're not sure where all the worksheet's comments are located, here are two ways to find them:

1) Use the Selection Pane

2) OR Run a macro

1) Use the Selection Pane

To see if there are hidden comments, or other objects on the worksheet, follow these steps:

  1. On the Excel Ribbon, click the Home tab
  2. At the far right, click the arrow on the Find & Select command
  3. Click Selection Pane

The Selection pane opens, and you can see a list of all the visible and hidden shapes on the worksheet.

  • Visible objects have an eye icon
  • Hidden objects have an eye icon with a diagonal line through it
Selection pane with list of objects on sheet
Selection pane with list of objects on sheet
Show the Comments

To see all the comments, so you can find the problems:

  • Click the Show All button at the top of the Selection Pane
  • Or, on the Excel Ribbon, click the Review tab, then click Show All Comments

2) Run a Macro

For a quick way to get a list of comments, use the macro shown below. (It is also in the sample file that you can download.)

The macro adds a new sheet to the workbook, with a list of comments, showing the cell address, and the position setting.

TIP: This list shows that there is a comment way off to the right on the worksheet, in column XFC. That might be causing the problem

macro lists comments and position property setting

To add this macro to your workbook, copy it to a regular code module.

Sub AllCommentsListProperties()
Application.ScreenUpdating = False

Dim rngCmts As Range
Dim c As Range
Dim wsA As Worksheet
Dim wsN As Worksheet
Dim i As Long
Dim lPos As Long
Dim myPos As String

Set wsA = ActiveSheet

On Error Resume Next
Set rngCmts = wsA.Cells _
    .SpecialCells(xlCellTypeComments)
On Error GoTo 0

If rngCmts Is Nothing Then
   MsgBox "no comments found"
   Exit Sub
End If

Set wsN = Worksheets.Add

wsN.Range("A1:B1").Value = _
     Array("Address", "Position")

i = 1
For Each c In rngCmts
   With wsN
     i = i + 1
     On Error Resume Next
     lPos = c.Comment.Shape.Placement
     Select Case lPos
      Case 1
        myPos = "Move/Size"
      Case 2
        myPos = "Move Only"
      Case 3
        myPos = "No Move/Size"
     End Select
     
     .Cells(i, 1).Value = c.Address
     .Cells(i, 2).Value = myPos
   End With
Next c

wsN.Range("A1:B1").EntireColumn.AutoFit
Application.ScreenUpdating = True

End Sub

Change Comment Property

After you find all the comments, change each comment's object positioning property, so it will move, if necessary. You can:

Change Comment Property Manually

Follow these steps to change comment's Object Positioning property:

  1. If the comment isn't showing, right-click the cell with the comment, and click Show/Hide Comment
  2. Right-click on the border of the comment, and click Format Comment
  3. Click the Properties tab
    • If you only see a Font tab, click Cancel, and try again
    • Be sure to click the comment border, don't click inside the comment
  4. For Object Positioning,
    • choose Move and size with cells
    • OR, choose Move but don't size with cells
  5. Click OK, to apply the change
Format Comment dialog box - object positioning
Format Comment dialog box - object positioning

Macro to Set Comment Property

This macro changes the Object Positioning property for each comment on the active sheet, to Move and Size

  • NOTE: If you'd rather use the Move but don't size setting, change the code from xlMoveAndSize to xlMove
Sub AllCommentsMoveAndSize()
'www.contextures.com
' /xlcomments03.html
Dim ws As Worksheet
Dim cmt As Comment
Set ws = ActiveSheet
  For Each cmt In ws.Comments
    cmt.Shape.Placement _
      = xlMoveAndSize
  Next cmt
End Sub

More Fixes for Shift Objects Error

I wrote about this Excel error on my Contextures Blog, and here are other fixes for this problem, based on reader comments.

  1. Greg found and deleted 4 comments, and that solved the problem in his workbook. To find the comments, he: clicked the Review tab at the top of Excel. Then, in the Comments group, he clicked the Next button.
    • In newer versions of Excel, click the down arrow in the Notes group, then select Next Note.
  2. Judy solved the problem by removing hyperlinks from all her graphics.
  3. Shane fixed the problem by moving the comments, instead of deleting them.

Download Sample File

To see the sample worksheet and macros from this page, download the Cannot Shift Objects sample file. The zipped Excel file is in xlsm format, and contains the macros from this page. Be sure to enable macros when you open the file, if you want to test the macros.

More Tutorials

Excel Comment Macros

Excel Comments -- Tips

More Comment Macros

Threaded Comment Macros

 

More Tutorials

Excel Comment Macros

Get weekly Excel tips from Debra

 

Last updated: March 25, 2021 11:13 AM
Contextures RSS Feed