Add page breaks to sorted data using VBA in Microsoft Excel


In this article, we will create a macro to insert page breaks, based on a certain condition.

Raw data consists agent wise data, which includes Agent name, Client name, Phone number and Email id.




We want data of a particular agent on separate pages while printing. So, we want to insert a page break at the end of each agent's data.


Logic explanation

We have created “InsertingPagebreak” macro to insert a page break. We make comparison of values of 2 consecutive cells in the first column to find the last cell related to a particular agent and insert a page break.

Code explanation


The above code is used to remove any previous page breaks inserted in the sheet.

If Cells(LngRow, LngCol).Value <> Cells(LngRow - 1, LngCol).Value Then

The above code is used to compare consecutive cells within the first column.

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(LngRow, LngCol)

The above code is used to insert page breaks before specified cells.


Please follow below for the code

Sub InsertingPagebreak()

'Declaring variables
Dim LngCol As Long
Dim LngRow, MaxRow As Long

'Clear existing page breaks

LngCol = 1

'Getting row number of last cell
MaxRow = Range("A11").SpecialCells(xlCellTypeLastCell).Row

'Looping through all the rows starting from thirteenth row
For LngRow = 13 To MaxRow

    'Comparing values in cell of two consecutive rows for the specified column
    If Cells(LngRow, LngCol).Value <> Cells(LngRow - 1, LngCol).Value Then
        'Inserting page break
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(LngRow, LngCol)
    End If
Next LngRow

End Sub


If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.