|   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 . All Excel worksheets created in Excel 97 and above are 256 columns wide (A to IV) and 65,536 rows long. More often than not, your worksheet uses only a small percentage of the cells available to you. A nice bit of spring cleaning limits the worksheet's scrollable area to just the part containing the data you want a user to see. You then can place data you do not want a user to see outside the scrollable area. Doing this also 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. The easiest way to establish boundaries is simply to hide all the unused columns and rows. On your sheet, locate the last row containing data and select the entire row below it by clicking the row label. Press the Ctrl and Shift keys while pressing the down arrow to select all rows beneath . Select Format  Row Hide to hide them all. Do the same thing for unused columns; find the last-used column, select the entire column to the right of it, press the Ctrl and Shift keys while pressing the right arrow, and select Format Column Hide. If all went according to plan, your useful cells should be surrounded by a gray moat past which you cannot scroll. The second way to establish boundaries is to specify a valid range in the worksheet's Properties window. Right-click the sheet's tab at the bottom left of the window and select View Code from the context menu. Select View Project Explorer (Ctrl-R) on Windows, or press  -R under Mac OS X to visit the Project Explorer. If the Properties window isn't visible, press F4 to make it appear. 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 (shown in Figure 1-12), go down to the ScrollArea property. In the associated value field to the right, enter the preferred boundaries of your worksheet $A$1:$G$50 , for instance. Figure 1-12. Project Explorer Properties window  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:G50" End Sub As usual, press Alt/ -Q to return to Excel proper 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.  | Any macro that tries to select a range outside this scroll area (including selections of entire rows and columns) will no longer be able to do so. This is true particularly for recorded macros, as they often use selections. | |
If your macros do select a range outside the scrollable area, you can easily modify any existing macros so that they are not limited to a specific scroll area while operating. Simply select Tools Macro Macros... (Alt-F8), locate your macro name, select it, and click Edit. Place the following line of code as the very first line of code: ActiveSheet.ScrollArea = "" As the very last line of code in your macro, place the following: ActiveSheet.ScrollArea = "$A:$G" So, your code should look something like this: Sub MyMacro( ) ' ' MyMacro Macro ' Macro recorded 19/9/2003 by OzGrid.com ' ' ActiveSheet.ScrollArea = "" Range("Z100").Select Selection.Font.Bold = True ActiveSheet.ScrollArea = "$A:$G" Sheets("Daily Budget").Select ActiveSheet.ScrollArea = "" Range ("T500").Select Selection.Font.Bold = False ActiveSheet.ScrollArea = "$A:$H" End Sub Our recorded macro selects cell Z100 and formats it to boldface. It then selects the worksheet named Daily Budget, selects cell T500 on that sheet, and "unbolds" it. We added ActiveSheet.ScrollArea = " " so that any cell on the worksheet can be selected and then the scroll area can be set back to our desired range. When we select another worksheet ( Daily Budget ), we again allow the code to select any cell on this worksheet and set the scroll area for this worksheet back to the desired 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 Tools Macro Visual Basic Editor, then select Insert Module, and enter the following code: Sub ResetScrollArea( ) ActiveSheet.ScrollArea = "" End Sub Now press 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 Tools Macro Macros... (Alt/Option-F8). 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. | 