EXCEL: FYI: Resetting the Vertical Scroll Bar!

Esteemed Forum Participants and Lurkers:
===============================

I just came across (quite by accident) the answer to a question that has been bugging me for a very long time ... how to reset the Vertical Scroll Bar on the right edge of an Excel Worksheet.

Occasionally, users do some nasty things with sheets, such as color fill or border format entire columns or whatever, and sometimes this upsets the Vertical Scroll Bar so that the bottom of the scroll is set to the absolute bottom of the sheet ... which is row 65,536. The downside of this is that the vertical scroll slider is virtually useless for scrolling where only a couple of hundred rows are actually used. This is a real nuisance. I never could figure out how to reset the scroll control ... until now!

Code:

---

Option Explicit
' Do NOT run CrashVertScroll on a SHARED WORKBOOK !!!
'
' GLOBAL Range Object variable
Public arange As Range
'
'Crash the Vertical Scroll Control
Sub CrashVertScroll()

    ' This properly sets the bottom vertical scroll to row 65,536
    Range("A65536").Value = "TEST"
    ' This clears the entire bottom half of the sheet,
    ' but it leaves the scroll  control brain dead!
    Set arange = Range(Cells(32768, 1), Cells(65536, 1)).Rows.EntireRow
    arange.Delete
   
    'There is NOTHING from Row 32768 to the end of the sheet, but the
    'vertical scroll bar is still set to scroll to the end of the sheet!

End Sub
'
' Here is how to reset it!  TRIVIAL!
Sub ResetVertScroll()

    Set arange = ActiveSheet.UsedRange
   
End Sub

---

It seems that forcing Excel to determine the current Used Range is what sets the scroll bar! It doesn't work just setting a range ... you have to reference the UsedRange property. Just thought this might be of interest.