Cannot hide columns/rows

[PO]

Excel 2003, sp2

Hi,

I'm trying to hide columns X:IV so that the user only can see columns A:W.
However Selection.EntireColumn.Hidden = True raises an error - "The hidden
property cannot be set for the range class" (roughly translated from
swedish). As far as I can see there aren't any objects in those columns. I
also tried selection.clear but it still doesn't work. The area is totaly
empty. If I try to hide the columns manualy from within excel I get the
error message "Object cannot be moved outside sheet".

Any ideas?

Regards
Pete

 

[Rick Rothstein \(MVP - VB\)]

Try this...

Columns("X:IV").EntireColumn.Hidden = True

Rick

 

[PO]

That doesn't work either. If I try the same code on a new worksheet it
works. It's just this sheet which contains some formulas and formatting -
nothing special. As I said, the cells are empty, they don't contain any
named ranges and they aren't part of any print area. It's some kind of a
mystery really

Regards
Pete

 

[Rick Rothstein \(MVP - VB\)]

You have your sheet protected, don't you? Try these 3 lines instead...

ActiveSheet.Unprotect
Columns("X:IV").EntireColumn.Hidden = True
ActiveSheet.Protect

or, if not running from worksheet's code window, change the ActiveSheet
reference to the worksheet's name reference...

Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Columns("X:IV").EntireColumn.Hidden = True
Worksheets("Sheet1").Protect


Rick

 

[PO]

Hi

Neither the sheet nor the book are protected. The funny thing is, if I
broaden col W (columnwidth = 39) I can hide the rest of the columns.

Pete

 

[PO]

Solved it. It turnes out that one of the comments is to broad. It's reaching
into col X when visible.

Regards
Pete

 

[Rick Rothstein \(MVP - VB\)]

Well, I would not have ever thought of **that** as the problem. Thanks for
posting that you found the problem and what the solution was.

I did some testing on different cells and found some differences in how
Excel reacts to comments. Most of the time, simply having the comment touch
a cell, even partly, was enough to block its column from being hidden;
however, in some other cases, a partially touched column was able to be
hidden and, when that happened, the comment was physically shown on the
other side of the cell it was attached to (with the pointer arrow going
straight across the cell). Now I didn't test too much, but I could not find
anyway to control making this happen (I was able to make it happen only
twice).

Rick