Dynamic Scroll Area

[jswalsh33]

In response to a previous question of mine on Scroll increment, Dave Peterson
referred me to Debra Dalgleish's site
http://contextures.com/xlfaqApp.html#Unused . I was able to get my scroll
increment to behave properly with the code:
LN = Sheets("Comp").Range("B11") + 10
Set rng1 = Cells(1, 1)
Set rng2 = Cells(LN, 19)
Set rng = Range(rng1, rng2)
Sheets("Lists").ScrollArea = rng.Address
A = LN & ":50000"
Sheets("Lists").Rows(A).Locked = False
Sheets("Lists").Range(Cells(LN, 1), Cells(50000, 1)).EntireRow.Delete

However, the result is not really dynamic, since one has to shut down and
restart Excel to have the new scroll range to take effect, which Debra's site
acknowledges for "older versions of Excel."

I am using Excel 2003. Is there a way to defeat the need to restart Excel?

Regards All,

Jim

 

[Patrick Molloy]

I have Excel 2003 and i don't need to reopen the spreadsheet


I use A1 and A2 on sheet2 to define the upper left and bottom right cells

Sub setscroll()
Dim myrange As String
myrange = Sheet2.Range("A1") & ":" & Sheet2.Range("A2")
Sheet1.ScrollArea = myrange
End Sub

when I change the values in A1 and A2 and run my code, the scroll area in
Sheet1 is changed.
I've never heard of having to restart before.

 

[jswalsh33]

Patrick,

I am sorry, I was not specific enough in specifying my problem. You are
correct, the scroll area does change without restarting Excel.

My problem is the increment of the scroll bar. My scroll areas are widely
different vertically, from about 50 rows to 2500 rows. When I run the 2500
row version, the vertical scroll bar becomes very small and the number of
rows when incremented on moving the bar becomes very large. When I switch
back to a 50 row version the bar remains very small and the increment very
large. If I restart Excel and select the 50 row version, the scroll bar
becomes large and the increments small. This is the problem discussed in
Debra's site.

I was looking for a way around restarting Excel other than making separate
worksheets of the two versions.

Thanks for your help.

Jim