Categories
Excel

Limit or Reset the Scrolling Range of a Worksheet

If you move around your spreadsheet a lot, or if you have data you don’t want readers to explore, you might find it convenient to limit the visible area of your spreadsheet to only that which has actual data.

  1. Limit Scroll Area by Hiding Columns and Rows
  2. Limit Scroll Area using VBA
  3. Reset Scroll Area by Calling a Macro

All Excel worksheets created in Excel 2007 and above are 16384 columns wide (A to XFD) and 10,48,576 rows long. The older versions (Excel-97 to Excel-2003) are 256 columns wide (A to IV) and 65,536 rows long.

Limit scrolling can make it less daunting to scroll around in a worksheet, as it is not uncommon for users to find themselves at row 50,000 and then start screaming that they are unable to find any data in a worksheet.

Hide Unused Columns & Rows to Limit the Scroll

The easiest way to establish boundaries is simply to hide all the unused columns and rows.

  1. On your sheet, locate the last row containing data and select the entire row below it by clicking the row label.
  2. Press End key to activate the End Mode.
  3. Press Shift+Down Arrow keys to select all rows beneath.
  4. To hide all selected rows:
    • Right-click on the selection and click Hide from the context menu.
    • Or, click Home » Cells Group » Format » Hide & Unhide » Hide Rows
      (for older versions, click Format » Row » Hide)
  1. Do the same thing for unused columns; find the last-used column, select the entire column to the right of it by clicking the column label
  2. Press End key to activate the End Mode.
  3. Press Shift+Right Arrow shortcut key to select all unused columns
  4. To hide all selected columns:
    • Right-click on the selection and click Hide from the context menu.
    • Or, click Home » Cells Group » Format » Hide & Unhide » Hide Columns
      (for older versions, click Format » Column » Hide).

If all went according to plan, your useful cells should be surrounded by a gray moat past which you cannot scroll.

Limit the Scroll Area with VBA

The second way to establish boundaries is to specify a valid range in the worksheet’s Properties window.

  1. Right-click the sheet’s tab at the bottom left of the window
  2. Select View Code from the context menu.
  3. Visit Project Explorer (press Ctrl+R or press ⌘+R if Project Explorer is not displayed).
  4. If the Properties window isn’t visible, press F4 to make it appear.
  5. Select the appropriate worksheet and visit the ScrollArea property in the Properties window.

Now, from within the Project Explorer, select the worksheet you want the scroll area limited to, and then, from the Properties window, go down to the ScrollArea property. In the associated value field to the right, enter the preferred boundaries of your worksheet-$A$1:$C$9, for instance.

Press Alt+Q to return to the sheet, you will be unable to scroll outside the area you have specified.

Unfortunately, Excel will not save this setting after closing it. This means you need a very simple macro to automatically set the scroll area to the desired range by placing some code in the worksheet_Activate event.

Right-click the Sheet Name tab on which the scroll area should be limited and select View Code, then enter the following:

Private Sub Worksheet_Activate ( )
 Me.ScrollArea = "A1:C9"
End Sub

The Me keyword refers to the object whose module the code is in. In this case, it’s the Sheet5 module, so Me refers to Sheet5.

As usual, press Alt+Q or ⌘+Q to return to Excel and save your workbook.

Although you will not see a visible clue, such as the gray moat of the first method, you won’t be able to scroll or select anything outside the specified area.

Reset Scroll Area with Macro

The preceding code will run automatically each time you activate the worksheet in which you placed it. If for some reason you need to clear the scroll area limits, you can remove the restriction with this statement:

Me.ScrollArea = ""

For example, you might encounter a problem when you need to actually enter data outside the existing used range. To avoid this problem, simply use a standard macro that will reset your scroll area back to the full sheet.

  1. Press Alt+F11 to open the VBA window (if not already opened)
  2. Select Insert » Module
  3. Enter the following code:
Sub ResetScrollArea( )
    ActiveSheet.ScrollArea = ""
End Sub

Assigning Shortcut Key to Macro

  1. Press Alt+F8 (or Opt+F8) to open the Macro dialog box, or
    Select View » Macros » View Macros
    (for older versions, select Tools » Macro » Macros).
  2. Select ResetScrollArea (the name of your macro)
  3. Click Options.
  4. Assign a shortcut key.
  5. Click OK

Each time you need to add data outside the established bounds of your worksheet, run the ResetScrollArea macro to readjust the borders. After you run the macro, make any changes you were unable to make while the scroll area was limited.

When you’re finished, activate any other worksheet and then activate the worksheet you just modified. Activation of the worksheet will cause the code to run and limit the scroll area to the desired range.

Understanding Workbooks and Worksheets: