Conditional Page Breaks

Excel is a handy tool for keeping track of all sorts of data. Many people use it at work to create ad-hoc reports for different departments or projects. As you work with your data, you may wonder how you can automatically insert page breaks when the contents of a certain column change. For instance, you might have a column that contains department names, and you may want each department to start on a new page.

This is rather easy to do with the built-in Subtotals feature of Excel. All you need to do is follow these steps:

1. Make sure your table contains column labels. For instance, if column A contains the department names, then cell A1 could contain a label such as "Department." Make sure all the columns have labels.
2. Sort the data in your table, using the department column as the key.
3. With any cell within the table still selected, choose Subtotals from the Data menu. Excel displays the Subtotal dialog box. (Click here to see a related figure.)
4. Using the At Each Change In drop-down list, select Department.
5. Using the Use Function drop-down list, select Count.
6. Using the Add Subtotal To list, select the name of the column where you want your subtotal to appear.
7. Make sure the Page Break Between Groups check box is selected.
8. Click on OK. Excel adds the subtotals and the page counts, as directed.

If, for some reason, you don't want to use the Subtotals feature, you can always write a macro that will remove all the page breaks in your worksheet, then add new page breaks at the appropriate places. The following macro will do the trick:

Sub PageBreak()
    Dim CellRange As Range
    Dim TestCell As Range

    Set CellRange = Selection
    For Each TestCell In CellRange
        ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakNone
        If TestCell.Value <> TestCell.Offset(-1, 0).Value Then
            ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakManual
        End If
    Next TestCell
End Sub

To use the macro, simply select the cells you want to use as your key for doing the splits, minus the top cell. For instance, if the departments are in column A, rows 2 through 37, you would select the range in A3 through A37. Run the macro, and any old page breaks are removed and new ones added.