Controlling Page Breaks Automagically

When it comes time to generate printed output from your Excel application, one sometimes finds that the standard toolset falls a bit short, and additional work must be done to get the pages looking just right. This is especially true in situations where the shape and size of the output is not predictable. For example: when the report is the output of a query or process, rather than a standard page template, it may not be possible to know how many rows, columns or pages may be involved.

Excel does give you access to many powerful printing control features, such as forcing the output to fit within a set number of printed pages (either width or height.) Most of these settings can be applied at design time, but one particularly tricky thing to manage is automatic page breaking.

An example Excel model contained a set of financials: an Income Statement, Balance Sheet, and Cashflow analysis. Each statement is set up for several hundred potential line items, many of which will be blank or zero, according to the data being summarized. To avoid a huge, ugly report that wastes reams of paper and toner every time it is printed, logic was added to hide blank rows, resulting in a nice, compact set of reports. Unfortunately, there's no way to know ahead of time how many pages will be needed, and Excel will insert automatic page breaks with no regard for logical placement.

When you're working on a static worksheet, it's relatively simple to manually adjust these page breaks so they happen in logical places, but in an automated environment, this is not an option. So, the following routine was developed to ensure page breaks only fall where allowed.


Public Sub Paginate(ByRef ws As Worksheet)

 Dim intPages As Integer
 Dim blnBadBreak As Boolean
 Dim intCol As Integer, intRow As Long

'must be in pagebreakpreview for all page
'breaks to be visible to the code
 ws.Activate
 ActiveWindow.View = xlPageBreakPreview

 intCol = 6 'control column

'clear all manual pagebreaks
 ws.ResetAllPageBreaks

'forced hard breaks (those with xx in control col)
 For intRow = 1 To ws.UsedRange.Rows.Count
   If ws.Cells(intRow, intCol) = "xx" Then
     ws.HPageBreaks.Add Before:=ws.Cells(intRow, 1)
   End If
 Next

'move arbritrary breaks (chosen by Excel) up to
'next viable row.

TopOfLoop:

 intPages = ws.HPageBreaks.Count
 If intPages <= 1 Then GoTo Done 
 For idx = 1 To intPages    
    intRow = ws.HPageBreaks(idx).Location.Row    
    blnBadBreak = False    
    While Cells(intRow, intCol) = ""      
       blnBadBreak = True      
       intRow = intRow - 1
       If intRow = 1 Then Goto Done    
    Wend    
    If blnBadBreak Then      
       ws.HPageBreaks.Add Before:=ws.Cells(intRow, 1)      
       GoTo TopOfLoop    
    End If 
 Next 


Done: 
'restore normal view 
 ActiveWindow.View = xlNormalView 


End Sub


This code requires a control column on the worksheet - this can be a hidden column outside the print area.  Use this column to indicate where page breaks are acceptable (with a single "x" - between sub-sections or at spacing rows) or where they are absolutely required (with "xx" - the top of each report section, for example.)  When the routine runs, it will first force page breaks where the xx's have been placed.  Then it will scan the automatic page breaks that Excel has "suggested", and if they don't fall where allowed, the code will move them up to the next allowable position.  The main program loop must be iterated multiple times, because every time you set a manual page break, this will cause Excel to recalculate the positions of the automatic breaks below that.

Just call this routine before printing the sheet, and you will be sure that the page breaks won't split a graph in half or bisect a section that needs to be continuous.  Your reports will look superb every time, no matter what the data set is.