How do I update the Vertical scrollbar

[Ken Soenen]

When I bring up Excel, the vertical scrollbar "knows" where the last row is
on the worksheet. Or, in other words, if I drag the scrollbar all the way to
the bottom, then the last row with data in it is also at the bottom. My
problem is the following:

Let's say I have 1000 filled rows. Then I delete the last 500 rows. The
vertical scrollbar when dragged to the bottom still thinks the bottom 500
rows are still there. The only way I can get the scrollbar to update is to
exit Excel and come back in. Since I am doing my deletions in VB, I would
like to get the scrollbar updated in VB also. However, I can't even do it
manually!!
Any thoughts??
thanks,
ken

 

[Dave Peterson]

Debra Dalgleish shows techniques to reset that last used cell:
http://contextures.com/xlfaqApp.html#Unused

 

[Peter T]

Hi Ken,

If you say your scroll bars update when you re-open your file I assume there
were no formats in the deleted range. In which case simply a UsedRange
statement might reset, if you're lucky! If not see Debra Dalgleish's page as
suggested by Dave.

Sub test()
[a1:a1000] = 1
[a1:iv1] = 1
End Sub

Sub test2()
[a1:a1000].Clear
[a1:iv1].Clear
ActiveSheet.UsedRange
End Sub

Regards,
Peter T

 

[Ken Soenen]

Thanks Dave and Peter,
The ActiveSheet.UsedRange seems to do
the trick.

ken