Programming Excel with VBA and .NET

Save and Display Views

Views store printer and hidden range settings for a workbook. For example, you might create two views for a complex worksheet: one named Summary that hides detail rows and one named Detail that hides no rows. Users can then switch between those views easily. Similarly, you can use views to store printer settings to make switching between portrait and landscape modes more convenient.

To use views in Excel, start with the default settings you want to use and then follow these steps:

1. Choose Views → Custom Views → Add. Excel displays the Add View dialog.

2. Enter a name for the default view and click OK.

3. Select rows or columns to hide in the new view and choose Format → Row/Column → Hide.

4. Choose File → Print → Properties and set the printer properties to use in the view.

5. Repeat Step 1 and name the new view.

To switch between views in Excel:

1. Choose Views → Custom Views. Excel displays the Custom Views dialog.

2. Select the view to display and click Show.

Tip

Since autofilters work by selectively hiding rows, views can be used to store filter criteria. You can then quickly switch between criteria using the views.

To create a view in code, use the Add method of the CustomViews collection. For example, the following code creates Summary and Detail views for a worksheet:

Sub CreateViews( ) Dim ws As Worksheet, wb As Workbook Set ws = ActiveSheet Set wb = ThisWorkbook ' Create Detail view ' Show all cells. ws.UsedRange.EntireRow.Hidden = False ' Hide an unneeded header ...