Excel 2007 issue with hidden objects

[mufka]

I have a spreadsheet that is not terribly complex, it just has some calculations. The spreadsheet is many years old and has been converted over the various versions of Excel. I don't know how it was created. If I try to insert a row, I get the message "Cannot shift objects off sheet". If I hit CRTL-6, I can then insert a row. (CTRL-6 just turns on the display of hidden objects). With the hidden objects displayed (none that are actually visible either way from what I can see) the program slows to a crawl.

How can I find out what these hidden objects are and get rid of them?

 

[cheerio]

does this help:

http://blogs.msdn.com/excel/archive/2009/01/30/hidden-and-invisible-objects.aspx

 

[combo]

There is at least one object at the bottom of the worksheet. It can be invisible, for instance transparent rectangle without borders.
First try to go to last line and check it.
You can use macro (ALT+F11 to display vbe editor, insert module):

Sub PickShapes()
Dim sh As Shape
On Error Resume Next
For Each sh In ActiveSheet.Shapes
    sh.Visible = msoTrue
    sh.TopLeftCell.Select
    sh.Select
    If Err.Number <> 0 Then
        MsgBox "can't select shape in " & sh.TopLeftCell
        Err.Clear
    Else
        Stop
    End If
Next sh
End Sub

When the code breaks, go to excel and delete shape if necessary. Next return to vbe and hit continue.)

combo

 

[Lilliabeth]

The default setting for 'For objects, show' is "All" and Microsoft says there is a known bug that causes this message to occur if you change it from "All to "None". MS says the workaround is to change the setting back to the default of "All".

So it seems to me that you should want to know why is the file slow when this isn't set to "All".

http://office.microsoft.com/en-us/excel/HA102412411033.aspx

--Lilliabeth

 

[mufka]

Lilliabeth ,

The file is slow when it "is" set to all. It is fine when it is set to none.

cheerio,

That was where I started. I don't think it applies because Design Mode is not available.

combo,

I don't know how to identify the last line. Last row with content? When I run the macro it doesn't give me any feedback. I'm not very familiar with macros and VBE so I'll keep poking at it.

 

[Lilliabeth]

oops... yes I misspoke, I meant of course that since All is the default, then why is the file slow when it is set to All?

MS recommends setting it to All (which is the default) to work around the known bug.

Did you have a look at the link I posted?

--Lilliabeth

 

[Lilliabeth]

Let me try this again... this problem is a known bug. That is not a mystery. It is documented.

The mystery is why is the file slow unless you change the setting that MS recommends you not change.

--Lilliabeth

 

[combo]

I mean last line of the worksheet. Select any cell, holding CTRL+SHIFT keys pressed click down arrow as many times as you see the last row. Select any single cell in the last row and scroll the sheet hotizontally to see if any shapes are visible.
Excel worksheet has limited size, if a shape exists in the last row and it it is set to move with cells, it is not possible to insert row anywhere above (same with columns in case of non-empty last column).

combo

 

[mufka]

combo,
Last row is 65536 and there is nothing in it. But if I hit CRTL-END, it jumps to BH213. But the sheet is blank below row 154. I do notice that, for example, columns AH and AK seem to have some formatting assigned to them because, even though they are blank, the columns have I guess what you would call a fixed width. I see this when I double click the line between the columns (the way you tell the column to size to fit the data that exists in the row). Does that indicate something?

Lilliabeth,
I had seen the link that you provided. The thing that I don't understand is that "Nothing (hide objects)" was enabled by default and that is that state in which I could not insert rows. When I change it to "All" the program slows down. This was a new install of Excel 2007 (new PC) and the spreadsheet was last used in Excel 97.

 

[combo]

Both shapes and non empty cells (formulas or comments) can't be moved below the last row. In the above cases excel will describe the case. Formats do not block inserting rows.
In this specific case, have you checked whole row (from a to IV)? If nothing is visible, I'd recommend to run the code in my previous post (maybe with assistance of programmer if you have no evperience with vba). It will reveal and pick shape by shape.
The alternative solution can be selection of all the rows below used range and deleting them.

combo

 

[mufka]

I've tried deleting all of the rows and columns that are not used. It didn't help. Looks like the user might just have to live with it or recreate the spreadsheet.

 

[Lilliabeth]

Have you launched the Selection pane to see all objects on the worksheet (as suggested in the link provided by cheerio)?

(Home tab, Find and Select, Selection Pane)

--Lilliabeth

 

[Lilliabeth]

Also it seems to me that you should try making a copy and converting it to the current version. Since you said the last row is 65536, I guess you haven't tried this yet.

--Lilliabeth