XL: Views Don't Adjust to Inserted/Deleted Rows or Columns

• Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
• Microsoft Excel for the Macintosh, versions 4.0, 5.0
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, if you use the View Manager add-in to create a view on your worksheet, and you then insert or delete rows or columns within the print area of that view, these changes are not reflected when you print the view.

CAUSE

This behavior occurs because when the View Manager creates a view, it sets the print area using the location and size of the original print area, not specific cells. As a result, the area printed will be the same size block of cells as the original print area. Inserted rows or columns causes cells along the edge of the original print area to be pushed out of the view. If rows or columns have been deleted, cells just outside the original print area are included in the view.

WORKAROUNDS

To workaround this behavior when you add or remove cells within a view defined on your worksheet, do either of the following:

• Recreate the view by deleting the old view and creating a new one in its place with the following steps:

        1. From the View menu, choose View Manager (version 5.0) or from the
           Window menu, choose View (version 4.0).
  
        2. In the View dialog box, select the view you want to recreate from
           the Views list.
  
        3. Choose the Show button to display the original view.
  
        4. Set the new print area. In Microsoft Excel version 5.0, choose
           Page Setup from the File menu, select the Sheet tab and enter the
           range you want included in the Print Area box. Choose OK. In
           Microsoft Excel version 4.0, select the cells you want included
           and choose Set Print Area from the Options menu. Make any other
           changes you want reflected in the view.
  
        5. From the View menu, choose View Manager (version 5.0) or from the
           Window menu, choose View (version 4.0).
  
        6. In the View dialog box, select the view you want to recreate from
           the Views list and choose the Delete button. Choose OK to delete
           the view. Choose the Add button.
  
        7. In the Add View dialog box, type in the name you want to give the
           view. Under View Includes, select the options you want to include
           in the view, and choose OK.
  
      -or-
  
  

• In Microsoft Excel version 4.0 only, to create a print area that automatically adjusts, define the print area so that it refers to a defined name for that range. The defined name automatically adjusts when rows or columns are changed in the referenced cells. To use this method, do the following:

        1. Highlight a range and set the print area.
  

        2. With the range selected, choose Define Name from the Formula menu.
           Enter a name for the range (such as p_area1) and choose Define.
  
        3. From the Names In Sheet list box, select Print_Area.
  
        4. Delete the contents of the Refers To box for Print_Area and type
           "=p_area1" (without the quotation marks).
  
        5. Define the view and include print settings.
  
        6. Repeat for all other views using a different defined name for the
           print range.
  
  

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 255-256