Written by Allen Wyatt (last updated February 25, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you manually recalculate your workbooks, you are probably doing so because of the time it takes to do the ...Discover More
Want to get rid of all the zero values in a range of cells? This tip provides a couple of different ways you can ...Discover More
Write out a check and you need to include the digits for the amount of the check and the value of the check written out ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.