Dynamically limit scroll area of worksheet

[MRK_Toledo]

I have a workbook with 6 different worksheets.

I would like to limit the scroll area +1 or 2 rows and have this adjust
dynamically according to the amount of data on each worksheet.

Each worksheet is different in size, so a fixed reange for all sheets
will not work.

I have tried to set the scroll area in the properties box for each
worksheet, but this does not give me my +1 or 2 rows, which I
might not REALLY need, but it resets itself after I close then
reopen the workbook.

Any ideas?

 

[Richard Schollar]

Hi

You'll probably want to use some VBA code in the Worksheet code module, something along the lines of:

Private Sub Worksheet_Activate()
Dim l As Long
l = Range("A65536").End(xlUp).Row + 2
Activesheet.ScrollArea = "$A$1:$IV$" & l
End Sub

and amend the column IV to whatever you want.

Does this help?

Best regards

Richard