Restricting Scroll Area

[vba_monkey]

Hello,

I can use this code to retrict the scroll area of a worksheet to the used range:

Sheets(1).ScrollArea = Range(Sheets(1).UsedRange, Sheets(1).UsedRange(2, 2)).Address

Say, for example the scroll area has been restricted to $A$1:$M$69. You can scroll down until row 69 is at the top of the sheet and is the only visible line with blank space beneath it.

I would like to restrict it so that the scroll stops when Row 69 is at the bottom of the sheet without exposing the empty space beneath. Does anyone know how to achieve this?

Thanks

 

[ZVI]

You might leave unhidden the row below used range, like this:

Sub Test()
  With Sheets(1).UsedRange
    .Cells(1).EntireRow.Offset(.Rows.Count).Hidden = False
    .Worksheet.ScrollArea = .Address
  End With
End Sub