Vertical scroll bar too long, not resetting

[DKaur]

Hello,

I have a worksheet with lots of data on it and have created a macro that toggles the data. There is a hidden column which has true/false depending on the toggle and the macro goes through this column and hides the row accordingly. However, once this is done the vertical scroll bar becomes so long that when one scrolls up and down the sheet, there is no room for movement on the scroll bar. Just to clarify, you can still scroll up and down the sheet. Is there anyway to reset the scroll bar?

Excel 2010

Thanks for any help,
D

 

[Asala42]

Try this:

ActiveSheet.UsedRange.SpecialCells (xlCellTypeLastCell)

 

[DKaur]

Unfortunately not, still has a very long scroll bar

 

[twohawks]

Finally figured this out. I tried everything in regard to clearing and deleting, and I do mean everything.
I had even checked for a rogue comment placement... but that's where I failed !!

What I discovered in my case was that, in the course of editing, which included clearing data from fill-in cells in order to regenerate a new template, and I have been using (and further developing) this particular one for years... a comment became positioned way below the print area! I happen to have two macros from years ago for finding and resizing comments, and one for repositioning comments next to their host cells. Evidently I had run into this years ago ;^) Using my old macros fixed it for me.

Anyway, I recommend that, however you do it, you simply track down all your comments and check them.
One easy way to catch one that's 'gone off the reservation' is to:
1) put your sheet into Normal View, and then Page Break Preview
2) Zoom out to, say 25%
3) Show All comments (keep in mind, show all comments may already be on, but the feature may not be showing them due to some other procedure you have conducted in the past, ...so toggle it and even test if you have to to ensure you are able to view all the comments)
4) Drag the Scroll Button, or use your Page Up/Down keys, to scroll off the Pages Zone in the direction of the 'Rogue Zone'. What's nice about this is that in View as applied, you can easily see anything outside the Pages Zone.
5) You should be able to follow a 'rogue comment tether-line' (sorry, don't know the technical callout for that connector line) out to the comment.

From here you should know what to do as far as rangling that doggy in ;^)

I hope this helps someone else.

 

[mreggcel]

3 and a half years later, it helped me! It was driving me nuts and I tried all other solutions as well.

 

[Niefer2]

It helped me today.
Btw:

Sub FitComments()'Autofit comments sizes
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
    xComment.Shape.TextFrame.AutoSize = True
Next
End Sub


Sub ResetComments()
'Reset comments positions
Dim pComment As Comment
For Each pComment In Application.ActiveSheet.Comments
   pComment.Shape.Top = pComment.Parent.Top + 5
   pComment.Shape.Left = pComment.Parent.Offset(0, 1).Left + 5
Next
End Sub

 

[AlexExcel14]

For anyone still having this issue. I thought I tried everything, cleared all unused cells, deleted all unused cells, ran macros, searched for comments and nothing seem to work.

Then I right clicked the box in top left corner. The box with the little south-east arrow above row 1 and left of column A. There was an option to "delete note".

Worked for me.

 

[SaiyanMaverick]

For anyone still having this issue. I thought I tried everything, cleared all unused cells, deleted all unused cells, ran macros, searched for comments and nothing seem to work.

Then I right clicked the box in top left corner. The box with the little south-east arrow above row 1 and left of column A. There was an option to "delete note".

Worked for me.

Thank you!! I clicked the same arrow, to select the entire page, then right-clicked and chose "delete comment", thankfully my file had no comments I cared to keep, and it reset the vertical scroll bar that was acting up.