» Add page breaks to sorted data using VBA in Microsoft Excel

The tip submitted by Kevin from Sheffield UK

Sort the data before operate the macro.

Sub Pagebreak()

ActiveSheet.ResetAllPageBreaks  'Clear existing page breaks

Dim Rng As Range    'number of rows
Dim lngCOL As Long   'column number to use - user input
Dim lngROW As Long    'row count

On Error GoTo EndMacro
Application.ScreenUpdating = False

lngCOL = InputBox("Enter the column NUMBER to use", "Column page break")

Set Rng = ActiveSheet.UsedRange.Rows

For lngROW = 3 To Rng.Rows.Count  'start by comparing row 2 and 3
  If Cells(lngROW, lngCOL).Formula <> Cells(lngROW - 1, lngCOL).Formula Then
      ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(lngROW, lngCOL)
      Application.StatusBar = "Row: " + Format(lngROW)
  End If
Next lngROW
Application.StatusBar = "Done"

EndMacro:
Set Rng = Nothing
Application.ScreenUpdating = True

End Sub