Creating Page Breaks in Excel Automaticaly

November 10, 2001 - by Bill Jelen

"S" (why can't people sign their e-mail with at least a first name?) wrote today's question.

I am trying to automatically put page breaks into a table in Excel. I tried Data - Subtotals, but that forces me to add a Sum or a Count. I don't want any subtotals, just a page break whenever the value in column A changes.

This macro will solve the problem. For some reason, macros that change page breaks or page setups take a long time to run, but this will certainly be faster that doing the process manually. The macro assumes your data is in column A and that it starts in row 2.

Sub AddBreaks()
    StartRow = 2
    FinalRow = Range("A65536").End(xlUp).Row
    LastVal = Cells(StartRow, 1).Value
    
    For i = StartRow To FinalRow
        ThisVal = Cells(i, 1).Value
        If Not ThisVal = LastVal Then
            ActiveSheet.HPageBreaks.Add _
                before:=Cells(i, 1)
        End If
        LastVal = ThisVal
    Next i
End Sub