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.
Note:
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.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Got some pesky blank rows in your data that you want to get rid of? This tip provides a wide variety of methods you can ...
Discover MoreA common task for macros is to open and process a file you want imported into your workbook. If you need to identify the ...
Discover MoreExcel allows you to hide worksheets so that they aren't visible to those using your workbook. Hiding worksheets has a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments