[Excel VBA] How to Get Total Print Pages and Hide Page Break Lines

Introduction

When preparing Excel documents for printing, you might want to know exactly how many pages the document will generate. Additionally, while editing, you might find the dotted page break lines—which appear after a print preview—distracting.

With VBA, you can easily retrieve this information and control these display settings. This article explains two practical snippets: how to calculate the total number of print pages and how to toggle the visibility of page break lines.

How to Get the Total Number of Print Pages

You can calculate the total number of pages by counting the horizontal and vertical page breaks (PageBreaks) in the worksheet.

Code and Explanation

Sub ShowTotalPrintPages()

    ' Declare variables
    Dim targetSheet As Worksheet
    Dim horizontalBreaks As Long
    Dim verticalBreaks As Long
    Dim totalPages As Long
    
    ' Set the active sheet as the target
    Set targetSheet = ActiveSheet
    
    ' Get the count of horizontal page breaks
    horizontalBreaks = targetSheet.HPageBreaks.Count
    ' Get the count of vertical page breaks
    verticalBreaks = targetSheet.VPageBreaks.Count
    
    ' Calculate total pages
    totalPages = (horizontalBreaks + 1) * (verticalBreaks + 1)
    
    ' Display the result in a message box
    MsgBox "Total print pages for this sheet: " & totalPages
    
End Sub

The logic behind this calculation is simple: Number of Pages = Number of Page Breaks + 1.

• If there are 0 horizontal breaks, there is 1 horizontal page (0 + 1 = 1).
• If there is 1 horizontal break, there are 2 horizontal pages (1 + 1 = 2).

The total page count is obtained by multiplying the horizontal page count by the vertical page count. This code reflects the current print area settings and scaling.

How to Hide Page Break Lines

Once you view a Print Preview in Excel, dotted lines indicating page boundaries appear on the sheet. You can easily toggle this display on or off using VBA.

Code and Explanation

Sub HidePageBreakLines()

    ' Hide page break lines on the active sheet
    ActiveSheet.DisplayPageBreaks = False
    
End Sub

Sub ShowPageBreakLines()

    ' Show page break lines on the active sheet
    ActiveSheet.DisplayPageBreaks = True
    
End Sub

The .DisplayPageBreaks property controls this setting for the worksheet.

• ActiveSheet.DisplayPageBreaks = False: Hides the dotted lines.
• ActiveSheet.DisplayPageBreaks = True: Shows the dotted lines.

Registering these macros to your Quick Access Toolbar allows you to toggle the lines with a single click, making your editing environment cleaner.

Summary

In this article, we covered two useful VBA techniques regarding printing and display:

1. Get Total Pages: Calculated using (HPageBreaks.Count + 1) * (VPageBreaks.Count + 1).
2. Toggle Page Break Lines: Set the ActiveSheet.DisplayPageBreaks property to True or False.

Both methods require very little code but can be very effective for automating tasks or customizing your workspace.