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.
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.
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.
NOTE: In other versions of Excel, you might see this error message: Cannot shift objects off sheet.
There are two solutions shown below, to fix the Cannot Shift Objects Error problem.
Fix 1: Change an Excel Option
Fix 2: Change Comment Property
And if neither of those worked, there are a few more fixes that you can try
This fix is quick and easy, so try it first.
If you still see the error message, go to the next section, to try Fix 2
Instead of using the Ctrl + 6 shortcut, you can follow these steps to view or change the option setting manually:
If the first fix didn't solve the problem, try this comment property fix instead.
On your worksheet, the problem might be caused by:
Follow the steps below to:
If you're not sure where all the worksheet's comments are located, here are two ways to find them:
2) OR Run a macro
To see if there are hidden comments, or other objects on the worksheet, follow these steps:
The Selection pane opens, and you can see a list of all the visible and hidden shapes on the worksheet.
To see all the comments, so you can find the problems:
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
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
After you find all the comments, change each comment's object positioning property, so it will move, if necessary. You can:
Follow these steps to change comment's Object Positioning property:
This macro changes the Object Positioning property for each comment on the active sheet, to Move and Size
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
I wrote about this Excel error on my Contextures Blog, and here are other fixes for this problem, based on reader comments.
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.
Last updated: March 25, 2021 11:13 AM
Contextures RSS Feed