cannot shift objects off sheet, but also cannot find objects

[danielo]

Trying to hide columns gives error "cannot shift objects
off sheet." Try as I might, I cannot find any objects in
or near the offending columns; indeed, there are no
objects on the entire sheet. I went to
Tools>Options>View>Objects and selected "Show All," but
still nothing appears.

 

[Jim Rech]

An easier way to see if there are any objects on a sheet is through
F5-Special. Cell comments are objects too. Do you have any of them?

--
Jim Rech
Excel MVP
| Trying to hide columns gives error "cannot shift objects
| off sheet." Try as I might, I cannot find any objects in
| or near the offending columns; indeed, there are no
| objects on the entire sheet. I went to
| Tools>Options>View>Objects and selected "Show All," but
| still nothing appears.

 

[danielo]

Tried F5-Special, found no objects. No comments, no.
Still can't hide columns! Confusing....

 

[Jim Rech]

If you want to send me the file, or the problem sheet, I'd like to see it.
You can clear out any sensitive data, etc.

--
Jim Rech
Excel MVP
| Tried F5-Special, found no objects. No comments, no.
| Still can't hide columns! Confusing....
|
| >-----Original Message-----
| >An easier way to see if there are any objects on a sheet
| is through
| >F5-Special. Cell comments are objects too. Do you have
| any of them?
| >
| >--
| >Jim Rech
| >Excel MVP
| message
| >| >| Trying to hide columns gives error "cannot shift objects
| >| off sheet." Try as I might, I cannot find any objects
| in
| >| or near the offending columns; indeed, there are no
| >| objects on the entire sheet. I went to
| >| Tools>Options>View>Objects and selected "Show All," but
| >| still nothing appears.
| >
| >
| >.
| >

 

[danielo]

Thanks for the offer! In the process of cleaning it up to
send you, I discovered the solution: there was a cell
comment in a cell in a column that was already hidden. In
fact, there were a block of about 10 hidden columns, and
this comment was in a cell in the MIDDLE of this group.
When the comment was removed, all columns/rows in the
sheet hid just fine.

So now I'm even more confused!

Why did this cell comment not show up in the F5-Special
search? Is it that the search ignores hidden cells? That
seems silly.

Why did the "offending" cell, already hidden in a block of
hidden columns, not prevent that block of columns from
being hidden in the first place, and how did it affect the
hiding of a completely different block of columns
elsewhere in the sheet?

 

[Jim Rech]

Why did this cell comment not show up in the F5-Special search?

A cell comment is not an object for purposes of F5 Special but it is for
some other purposes. Has to do with when features were introduced into
Excel.

I wish I could tell you. It's pretty weird how and when this problem
arises.

 

[Jim Rech]

Btw, if you wanted to keep your comments where they were and be able to hide
columns, there is something you can do:

Select the cell with the comment and then press Shift-F2 to edit it. Now
right click the *edge* of the comment and pick Format Comment. On the
Properties tab change the positioning to the first choice - Move and Size
with Cells and click OK. That should do it.

--
Jim Rech
Excel MVP
| >>Why did this cell comment not show up in the F5-Special search?
|
| A cell comment is not an object for purposes of F5 Special but it is for
| some other purposes. Has to do with when features were introduced into
| Excel.
|
| >>Why did the "offending" cell, already hidden in a block of hidden
columns,
| >>not prevent...
|
| I wish I could tell you. It's pretty weird how and when this problem
| arises.
|
|
| --
| Jim Rech
| Excel MVP
|
|