Cant push objects off a sheet

Copper Contributor

Im on row 1081 of my excel sheet and when I try to insert a new row a pop up says I "Can't push objects off a sheet".

10 Replies

@CraigWood29 

Before you look at the proposed solution, and since we do not know your Excel version, operating system, storage medium, I recommend performing an update in advance. It is recommended to always provide this information in advance in order to get a quicker and more concrete solution.

 

This error message occurs when you attempt to insert rows or columns in a worksheet, and the option “Nothing (hide objects)” is selected under the “For objects, show” section in Excel options. This is a known bug in some versions of Excel. 

Although this option is not selected by default, it’s possible to set it accidentally by pressing a particular keyboard shortcut (CTRL+6).

To fix this issue, you can unhide all hidden rows and columns in your worksheet. 

You can do this by selecting all cells in your worksheet by clicking on the box at the top left corner of your worksheet (above row 1 and to the left of column A), then right-clicking on any row or column header and selecting “Unhide” from the context menu.

 

I hope that helps!

NikolinoDE

I know I don't know anything (Socrates)

@NikolinoDE 

I faced very same problem, i tried all the solutions mentioned above, but no avail. Then I noticed that I was able to hide all but two columns. I realized this was because there I had inserted a note in the last column and those two columns were required to show the notes. I edited the notes, moved it to left. Voila, I was able to hide all columns after my content

I am experiencing the 'Can't push objects off the sheet' error message when attempting to hide columns to work with vendor data.  None of the workarounds work and the error message seems to become a new feature of the hide function.  My only solution was to download a new copy of the workbook and to hope the popup doesn't come back

@Amy_Reese 

I am on Office365 for enterprise and the issue seems to begin when more than 12 colums are selected OR if the selection is close to the edge of the visual screen.  the popup may not occur if you do a smaller selection of columns, but once it happens, it will happen again, and effectively lock you out from hiding AND unhiding content.  super annoying as i use this function regularly 

I was unable to hide columns. So I found the faulty row first and then found the faulty cell of that row. As it turns out, the very last column contained a tool-tip comment. Deleting the comment fixed the problem.

I believe what was happening is that when I was hiding any column the overall width of the Excel work-area (not just the area you are using) would shrink, But Excel would internally continue to position the comment at the same physical location that was now out of bounds.

In diagnosing the problem, try to delete all comments. If that fixes the problem, find the comment that is causing it. That comment is probably near the very last row or column that Excel supports



To everyone that has this issue, @dkyrtata has the correct solution.

 

It seems that the old style Comments (called NOTES in Office 2019+) that 'span' to the right hand side for instance (hovers over into other columns), those columns are the columns that can't be hidden ("Can't push objects off a sheet").

 

The problem is my comments are hidden by default (only shows on hover) so I never realized the comments would overlap the target columns that I want to hide.

Manually MOVE the comments (notes) into the column it is showing information for or to the left such that nothing that will 'hover' will be in the overlapping the columns you want to hide (I usually hide everything to the right that is unused).

I imagine everything like the new conversation notes eg. that will 'hover' its contents in overlapping columns that you want to hide will do the same. 

It would appear that the 'hidden' section is 'out of bounds' even for Excel to render things.

 

Thanks for this, I was so confused!

@chameleondevil 

Just in case, we have both Comments and Notes on modern Excel.

image.png

In general behaviour is bit different, but that concrete case, yes, that doesn't matter.

You would think that Microsoft would focus their programming effort to prevent the error rather than show a message that nobody understands.

 

I found that even if you manually reposition the comment as you recommend, you can still encounter the error if you hide a sufficient number of columns; eventually the comment is out of bounds.

 

Microsoft's implementation of comments is a bit flaky; routinely growing or shrinking out of size, sometimes closed entirely so you cannot read them when hovering over the cells. Very annoying and time-consuming to resize them all,

 
 
 
 
 
 

@dkyrtata 


I agree that it is something that should be improved upon, in my opinion the error message is useless and means nothing in the context of this exact issue (if you didn't point out the Comments I would not have realized it myself since they were hidden).  Perhaps the 'canvas/out of bounds' area is read-only (since the old original Excel days. I guess we will not really ever know.

 

Regarding your manual movements, perhaps I have a small solution that you can adapt and use for your own use case.

I created something that may help you to start of with if you still may have need of it:

 

The below VBA code is just a starting point to show you that it is indeed possible with VBA, for your use case in your specific file you would need to modify further.

 

SETUP

The references mentioned here are used in the VBA code below
1. Create a new Workbook, inside it create a new Worksheet "TEST WORKSHEET"

2. Give a range of cells a named range "TEST_DATA"

3. Inside VBA (ALT+F11) create a new Module (right click on Modules folder -> create new Module)

4. Paste the code, and after reviewing it press F5 on your test file to see how it moves the stuff.

 

NOTE: THE HOVER EFFECT OF THE COMMENTS DOES NOT SEEM TO CAUSE THE ERROR WE ARE DISCUSSING HERE.

(WHEN YOU RIGHT-CLICK -> Edit notes) you will notice the comment box has moved slightly to the left and downwards.

 

Code does this: 

- Loops through the Range of Cells and try to find non-empty cells; and then try to find if they have comments attached. 
- For found comments it will Debug.Line to the VBA console but additionally move the comment box left-downwards relative to the active cell. 

 

In theory this helps you with moving the comments such that you can hide right-most columns (even those close/overlapping the hover effect) properly.

 

I created this as test code to see if it is possible to move the Comments boxes, and have since modified this much more into a working solution for my specific Excel file.

 

NOTE:

Inside the range of cells of TEST_DATA, you can have some empty values, and then add 'the old legacy style comments'.  If your workbooks is actually using Conversations; then the object is different (I think it is `cCell.CommentThreaded` but haven't tested to see how it works differently).

 

CODE

Public Sub ObjectInformation()
    Dim ws As Worksheet
    
    ' Change Worksheet name and Cell Range/Address ($A$Z format)
    Dim nameWorksheet As String: nameWorksheet = "TEST WORKSHEET"
    Dim nameDataRange As String: nameDataRange = "TEST_DATA"
    
    Set ws = ThisWorkbook.Worksheets(nameWorksheet)
    
    Dim rRange As Range
    Dim cCell As Range
    
    Set rRange = ws.Range(nameDataRange)
    
    For Each cCell In rRange
        ' Skip empty cells
        If cCell.Value <> "" Then
            ' Check for Notes (legacy comments)
            If Not cCell.comment Is Nothing Then
                Debug.Print "Note in cell " & cCell.Address & ": '" & cCell.comment.Text & "'"
                ' Attempt to report and update (move) bounds of Comment for current Cell
                getCommentBounds cCell.comment, cCell
                ' Attempt to report current Cell bounds information
                getCellBounds cCell
            End If
        End If
    Next
    
End Sub

Private Sub getCommentBounds(ByRef comment As comment, ByRef rCell As Range, Optional ByVal moveAndResizeToCell As Boolean = True)

    Dim commentShape As Shape: Set commentShape = comment.Shape
    Debug.Print "OLD Comment Details:"
    ' Get location (top-left corner) and size (width and height)
    Debug.Print "    Top: " & commentShape.Top
    Debug.Print "    Left: " & commentShape.Left
    Debug.Print "    Width: " & commentShape.Width
    Debug.Print "    Height: " & commentShape.Height
    
    ' Move Comment?
    If moveAndResizeToCell Then
        ' First reset shape to 0,0 of current Cell
        commentShape.Left = rCell.Left
        commentShape.Top = rCell.Top
        
        ' Non-hoverable (edit Comment) movement; move to Left of Cell (-10) and Downwards (10)
        commentShape.IncrementLeft -10
        commentShape.IncrementTop 10
        
        ' Reduce Comment such that it can at least fit inside the current Cell
        commentShape.Width = rCell.Width * 0.95
        
        Debug.Print "NEW Comment Details:"
        ' Get location (top-left corner) and size (width and height)
        Debug.Print "    Top: " & commentShape.Top
        Debug.Print "    Left: " & commentShape.Left
        Debug.Print "    Width: " & commentShape.Width
        Debug.Print "    Height: " & commentShape.Height
    
    End If

End Sub

Private Sub getCellBounds(ByRef cell As Range)
   
    Debug.Print "Cell Details:"
    ' Get location (top-left corner) and size (width and height)
    Debug.Print "    Top: " & cell.Top
    Debug.Print "    Left: " & cell.Left
    Debug.Print "    Width: " & cell.Width
    Debug.Print "    Height: " & cell.Height
   
    
End Sub

 

@mvumesh, What a great find! 👏 I had the same issue with a note and I bet I would have taken hours to find this if it wasn't for this comment. Great detective work 😀