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

 

[mole999]

i would just hide the remainder rows

 

[vba_monkey]

They are hidden but I'm still left with a big empty space when I scroll to the bottom of the sheet.

 

[Fluff]

How about

Sub chk()
Sheets(1).ScrollArea = Range(Sheets(1).UsedRange.Rows, Sheets(1).UsedRange.Columns).Address
End Sub

 

[vba_monkey]

No joy, that does the same as my original code.

 

[Fluff]

In that case it sounds like the usedrange is larger than expected.
one option might be

Sub chk()
Dim x
x = Sheets(1).UsedRange
Sheets(1).ScrollArea = Sheets(1).UsedRange.Address
End Sub

or

Sub chk()

    Dim UsdCols As Long
    Dim Usdrws As Long
    With Sheets(1)
        Usdrws = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        UsdCols = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).column
        .ScrollArea = .Range("A1", .Cells(Usdrws, UsdCols)).Address
    End With
End Sub

 

[vba_monkey]

Thanks, Fluff.

I've tried both your methods and they still produce the same result.

It looks like when the rows outside the used range are hidden, it produces the behavior I described and if they are unhidden it behaves how I want to. I may have to compromise and and keep them visible although it's not perfect.

I doubt there is a way around this but let me know if you think there is.
Ta

 

[Fluff]

Ok, how about

Sub chk()

    Dim UsdCols As Long
    Dim Usdrws As Long
    With Sheets(1)
        Usdrws = .UsedRange.SpecialCells(xlVisible).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        UsdCols = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).column
        .ScrollArea = .Range("A1", .Cells(Usdrws, UsdCols)).Address
    End With
End Sub

 

[vba_monkey]

Still the same I'm afraid. All of these methods set the same scroll area, it's just the way excel behaves when you scroll down depends on whether the rows outside of the scroll area are hidden or not.

 

[Fluff]

I set some hidden rows, ~100 rows below my data & used the code in post#8 & I could not scroll below the last visible row.
When you try it what value does UsdRws have? & is it the last visible row?
If not you may have cells containing spaces or the like.