Limit scrolling

  • Thread starter Thread starter JEM
  • Start date Start date
J

JEM

I have a worksheets that is 500 rows long. I want to limit scrolling to the
end of the worksheet. Is there a way to do this? Can't use hide rows, 'cause
it goes on forever...

Also, is there a way to limit the scroll from side to side? I.e., If the
sheet is 200 columns wide, have the scroll limit stop at the end of the
sheet. I have hidden the columns, but zooming out, you can see the empty
area.

Thanks
 
Hi Jem

Right click on the sheet name tab, select "View Code" and paste in this
code;

Private Sub Worksheet_Activate()
Me.ScrollArea = Me.UsedRange.Address
End Sub

It might be needed for you to, locate the last row of data. Then select
the Row below, now hold down Ctrl+Shift and hit the Down Arrow. Now go
to Edit>Clear>All



***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
JEM,

First (and you may have done this) you can hide all the rows beyond your
last row. The area beyond will turn gray.

To do that, select the row beyond your last one (click in the row header),
then extend the selection to the middle of the earth (bottom of the sheet)
with Ctrl-Shift-Down. Now right click in any selected row's header, and
Hide. You can do the same with columns beyond your data too.

Now to disallow scrolling into the gray area, you must set the ScrollArea
property to the sheet. Works great, only Excel forgets it the next time you
open the file, so you need a Workbook_Open event macro to do it each time
the file is opened.

Private Sub Workbook_Open()
Sheets("Sheet1").ScrollArea = "A1:GR400"
End Sub

Change GR400 to suit. GR is column 200

This means you'll get the macro warning dialog (unless your security level
is set dangerously low), and must allow macros for it to work.
 
Back
Top