Do you find that users frequently scroll
outside the data range and onto blank areas of their worksheet? You
can create a macro that will automatically prevent them from moving
outside the used range on a worksheet. Follow these steps:

  1. Activate the worksheet.
  2. Right-click the sheet name tab on which you
    want to limit scrolling and select View Code.
  3. Enter the following code:

    4. Private Sub Worksheet_Activate()
            Me.ScrollArea =
    Range(Me.UsedRange, Me.UsedRange(2, 2)).Address
         End Sub

  4. Press [Alt][Q] and save the workbook.

Each time you activate the worksheet, this
macro will run automatically to prevent scrolling outside the used
range.

When you need to enter data outside the used
range, you will need to reset the scroll area. You can create a
shortcut key that activates a macro to do just that. Follow these
steps:

  1. Press [Alt]
    [F11]
  2. Select Insert | Module and enter the
    following code at the prompt:

    3. Sub ResetScrollArea()
               ActiveSheet.ScrollArea
    =”
           End Sub

  3. Press [ALT][Q]
  4. Press [ALT][F8] and Select
    ResetScrollArea.
  5. Click Options and assign a shortcut key.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.