How to Remove PageBreaks in Excel with VBA

This post came about as a client of mine introduced a document classification product called Titus that works as an add-on to Microsoft Excel.  The Titus classification add-on caused an unexpected consequence, when you set the security classification of the document i.e. classify the document as confidential it automatically adds page breaks to every worksheet in the workbook.




Admittedly this is not a huge problem as the Excel file still works as intended but in visual terms us Excel analysts would prefer not to show page breaks unless they have been specifically requested, they make the file look unfinished.

This is a problem that is easily solved so let me share the solution with you now so if any other Excel users out there have experienced the same problem with Titus classification adding page breaks to Excel, or other packages doing something similar, you can now take quick and effective action.

 

To remove page breaks in Excel there are a couple of alternatives

Remove Page Breaks in Excel the manual way

This method is best suited for one-off actions or when you only have a couple of worksheets to remove the page breaks from.  It is achieved via the Excel Options menu and I have written a step-by-step guide that can be found here.

Remove Page Breaks in Excel with VBA

This method should be used to save time if you are regularly creating new Excel workbooks and have to remove the page breaks caused by setting the Titus classification or if you have a workbook with numerous worksheets that require the page breaks removed.

  1. To begin open the VBA Editor, either via the shortcut command of ALT+F11 or via Developer > Visual Basic in the Ribbon menu (no Developer tab in the Ribbon?  Click here to resolve)
  2. Insert a new Module in VBA using the menu selection of Insert > New Module.
  3. In the module code window insert the below code:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    
    Sub RemoveBreaks()
    'This removes all page breaks for all worksheets in the workbook
    'Created by www.DedicatedExcel.com
     
    Dim ws As Worksheet
     
        For Each ws In ThisWorkbook.Worksheets
            ws.DisplayPageBreaks = False
     
        Next ws
    End Sub

  4. Save the module and exit the VBA Editor
  5. You now have a macro in your workbook called RemoveBreaks that will remove all page breaks for every sheet in the workbook, no matter how many worksheets have been created.  You can access this macro with the shortcut command ALT+F8 or via Developer > Macros on the Ribbon as shown below:

If you use Excel frequently then it would be a good idea to favourite this page so you can copy the code into each workbook you create.  Alternatively you can save the code in your Personal Workbook so that it is available to you every time you create a new Workbook.  Read my guide on creating a Personal Workbook here if you would like to go down that method.

Keep Excelling,

Leave a Comment