Activating Only the Used Range
A third method, the most flexible, automatically limits the scroll area to the used range on the worksheet within which you place the code. To use this method, right-click the Sheet Name tab on which you want the scroll area limited, select View Code, and enter the following code:
Private Sub Worksheet_Activate( )
Me.ScrollArea = Range(Me.UsedRange, Me.UsedRange(2,2)).Address
End Sub
Now press Alt-Q or click the X in the top righthand corner to get back to Excel and save your workbook.
The preceding macro will run automatically each time you activate the worksheet in which you placed it. However, you might encounter a problem with this macro 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. Select Developer → Visual Basic under Code options (pre-2007, Tools → Macro → Visual Basic Editor), then select Insert → Module, and enter the following code:
Sub ResetScrollArea( )
ActiveSheet.ScrollArea = ""
End Sub
Nowpress Alt-Q or click the X in the top-righthand corner to get back to Excel and save your workbook.
If you want to, you can make your macro easier to run by assigning it to a shortcut key. Select the viewtab, then Macros, or press Alt/Option-F8 (pre- 2007, Tools → Macro → Macros...). Select ResetScrollArea (the name of your macro), click Options, and assign a shortcut key.
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.