Cannot find Tools-Option-Edit

[AlexanderBB]

After searching for half an hour!
I have Excel 2010.
Thanks

 

[MARK858]

Please explain.

I posted how to show and check for objects and comments, until you know exactly what you have then you shouldn't rule them out... trust what Excel tells you rather than what you think is there.

If Excel doesn't show you anything there when you check (which would be there normally if it is telling you in the error message that you are pushing objects off the sheet), then you look at other possible options.

 

[AlexanderBB]

I could make a backup and delete all objects and comments from the sheet. Then see if the column Hides.
But what good is that if the comments etc are wanted? Does it mean you have to remove them, hide the column, then put them back?
That doesn't seem right.
I'll try on a backup. I did Hide Col Z and then couldn't get it back - until cntrl-Z a few times. The Row where you normally see the Column letters
only goes from A-Z. With Col Z Hidden it changed to A-Y.
If you move or scroll to the right past Col Z. everything in plain grey. Something is differnet on this sheet.

 

[MARK858]

Check to see what and where the objects are, you don't need to delete all objects or comments, just the offending one's if there.

If you move or scroll to the right past Col Z. everything in plain grey. Something is differnet on this sheet.

Unhide all your columns, click the select all cells triangle, place your cursor over where the column letters are, right click, click unhide

 

[AlexanderBB]

>Unhide all your columns, click the select all cells triangle, place your cursor over where the column letters are, right click, click unhide
Yes, that unhid everything. Is it reversable?

On my backup the 3 objects (in Row 1) didn't seem to matter. That is removing them changed nothing.
So I removed all the comments and then the column would hide.
But I want to keep them, and there's one on every row.
So it is true you cannot unhide then rehide a column if you have comments? (The column to hide is not the one with comments.)
If that's the case, can you prevent "unhide" from firing?

 

[MARK858]

Yes, that unhid everything. Is it reversable?

Select the first column that you want to hide, Ctrl-Shift + right arrow, hover your mouse over the cells, right click, click hide

So I removed all the comments and then the coil would hide

Did you not have anything further to the right than the comments that you want to keep when you did the check for comments?

 

[MARK858]

You'll get the error if the comments are within the area being hidden if you are hiding All the columns to the last column, if you leave the last column visible then you don't get the error

 

[AlexanderBB]

I'm using cols A-Z with L, N-R & U hidden. Ones > Z I don't see /use and that's ok, it's quite good not seeing them.
I can unhide these and hide them again one by one until the last, whatever that is. There are no comments in those columns
There is one comment at Y1 and the rest in Col J, which is never hidden.
I've removed them all and the problem has gone. Am progressively putting them back to try and see where it plays up.
There's 7000 rows so may take a while.
I'd Understand getting the error if the comments are within the area being hidden, but they;re not in this case.
>Did you not have anything further to the right than the comments that you want to keep when you did the check for comments?
Only the one at Y1. I'll put him back last.
Be good to nail this and I appreciate your time.

 

[MARK858]

Ones > Z I don't see /use and that's ok, it's quite good not seeing them

I can unhide these and hide them again one by one until the last,

Ctrl-Shift + right arrow, Shift + left arrow, hover your mouse over the cells, right click, click hide

There's 7000 rows so may take a while.

You should be able to select all the cells, Ctrl + G, Special, Comments, ok, right click and remove the comments in one go

I'd Understand getting the error if the comments are within the area being hidden, but they;re not in this case.

That might be a version issue as there were some quirks in some versions when hiding columns/rows, only seems to happen with my 365 if they are in the area

 

[Peter_SSs]

I have Excel 2010.

Does that mean you should be updating your profile which shows 2019 & 2016?

I'd Understand getting the error if the comments are within the area being hidden, but they;re not in this case.

The comments may not be in the area to be hidden but the pop-up comment box may well be.

In the image below all columns to the right of AB are hidden. You will note that the pop-up comment box covers, or partly covers, 2 of the 3 remaining columns to the right of the actual comment.
column (Y). I am able to hide any one of columns Z, AA and AB. If I try to then hide another one I cannot because the comment box would then be (partially) off the worksheet.

If I start with my sheet as per the image above and first resize my comment box like below, then I can hide any two of the right columns, but not all three. This may be an option for you?

If you need your col Y comment to be wider than col Z you could try one of these two things. I do not know how they will go in your Excel version but they worked for me
Option 1: Edit the Comment (they are called Notes in my version but Comments in older versions) to move it away.

Option 2: Delete the col Y note, hide columns to the right of Z and then re-insert the note. For me it now shows like this

 

[AlexanderBB]

This has been very useful, thanks to you both and Peter's examples.
I tracked down to first offending comment on row 1447. It was very wide and although it fitted, there wasn't room to
hide anything as it would have then "moved off the sheet". It does make sense now. And why it was originally hidden - the comment was
added with those columns hidden. Explains why it could unhide but not re-hide.
I think it's possible to get a comment size so I'll hunt for that and map them, altering those that need it. This one on row 1447 has only one line
too long and could be LF split.
One question please, on another worksheet some comments have lost their size only showing part of the pop-up. I can delete and
re-do them but maybe they just need autosize reset ? When created Autosize is True and AutoShapeType = msoShapeRoundedRectangle
and some text is bolded or differnt colors.
I edited the profile version Peter, Office 2019 is on one computer and 2010 on another. The latter is used the most.