Inserting a Page Break Every X Rows

Maria has a worksheet in which she wants to start a new page after every X number of rows. This break is not dependent on any data in the worksheet, simply on the number of rows. (For instance, she may want to start a new page after every 17 rows.) Maria wonders if there is a way to insert a repeating page break that is solely dependent on the number of rows.

There is no way to do this through a setting or through any conditional formatting. The only way we've been able to accomplish this is through the use of a macro. The following macro provides a bit of interaction to increase its flexibility.

Sub BreakEveryX()
    Dim iGap As Integer
    Dim lLastRow As Long
    Dim lRow As Long
    Dim sTitle As String
    Dim bGo As Boolean
    Dim sTemp As String

    sTitle = "Set Page Breaks"
    bGo = True

    sTemp = InputBox("Enter rows per page:", sTitle)
    iGap = Val(sTemp)
    If iGap > 0 Then
        sTemp = InputBox("Last row for page breaks:", sTitle)
        lLastRow = Val(sTemp)
        If lLastRow >= iGap Then
            With ActiveSheet
                .ResetAllPageBreaks
                For lRow = iGap + 1 To lLastRow Step iGap
                    .HPageBreaks.Add Before:=.Cells(lRow, 1)
                Next lRow
            End With
        Else
            bGo = False
        End If
    Else
        bGo = False
    End If

    If Not bGo Then
        MsgBox Prompt:="No changes made", Title:=sTitle
    End If
End Sub

All you need to do is to display the worksheet you want to affect, and then run the macro. You are asked for how many rows you want per page (for Maria's example, that might be 17) and the row you want to stop at. The macro then removes all existing page breaks and inserts a page break after each multiple of the per-page rows you specified. If you enter 0 for the rows per page or if the ending row number is less than the rows per page, then no changes are made and you see a message box to that effect.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13023) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.