Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Conditional Page Breaks.

Conditional Page Breaks

by Allen Wyatt
(last updated June 26, 2017)

10

Excel is a handy tool for keeping track of all sorts of data. Many people use it at work to create ad-hoc reports for different departments or projects. As you work with your data, you may wonder how you can automatically insert page breaks when the contents of a certain column change. For instance, you might have a column that contains department names, and you may want each department to start on a new page.

This is rather easy to do with the built-in Subtotals feature of Excel. All you need to do is follow these general steps:

  1. Make sure your table contains column labels. For instance, if column A contains the department names, then cell A1 could contain a label such as "Department." Make sure all the columns have labels.
  2. Sort the data in your table, using the department column as the key.
  3. Display the Data tab of the ribbon.
  4. With any cell within the table still selected, click the Subtotal tool in the Outline group. Excel displays the Subtotal dialog box. (See Figure 1.)
  5. Figure 1. The Subtotal dialog box.

  6. Using the At Each Change In drop-down list, select Department.
  7. Using the Use Function drop-down list, select Count.
  8. Using the Add Subtotal To list, select the name of the column where you want your subtotal to appear.
  9. Make sure the Page Break Between Groups check box is selected.
  10. Click on OK. Excel adds the subtotals and the page counts, as directed.

If, for some reason, you don't want to use the Subtotals feature, you can always write a macro that will remove all the page breaks in your worksheet, then add new page breaks at the appropriate places. The following macro will do the trick:

Sub PageBreak()
    Dim CellRange As Range
    Dim TestCell As Range

    Set CellRange = Selection
    For Each TestCell In CellRange
        ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakNone
        If TestCell.Value <> TestCell.Offset(-1, 0).Value Then
            ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakManual
        End If
    Next TestCell
End Sub

To use the macro, simply select the cells you want to use as your key for doing the splits, minus the top cell. For instance, if the departments are in column A, rows 2 through 37, you would select the range in A3 through A37. Run the macro, and any old page breaks are removed and new ones added.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7991) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Conditional Page Breaks.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Alternative Ways of Creating Random Text

You can use a built-in Word feature (RAND) to create random text, but such text may not be to your liking. This tip explores ...

Discover More

Deleting Worksheet Code in a Macro

When creating an application in VBA for others to use, you might want a way for your VBA code to modify or delete other ...

Discover More

Counting Employees in Classes

Excel is very good at counting things, even when those things need to meet specific criteria. This tip shows how you can do a ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Highlighting Cells Containing Specific Text

If you want to highlight cells that contain certain characters, you can use the conditional formatting features of Excel to ...

Discover More

Coloring Identical Company Names

Want to know where duplicates are in a list of names? There are a couple of ways you can go about identifying the duplicates, ...

Discover More

Copying Conditional Formatting

Conditional formatting is a great feature in Excel. Here's how you can copy conditional formats from one cell to another in a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 0?

2017-06-26 10:30:58

Gary Lundblad

How would I have a page break created automatically, if possible, when the number 13 appears in column K of a worksheet? That is, I want a page break after the row with a value of 13 in row k, or before the row with a 14, whichever is easier. I'd prefer not to have to run a macro each time, as I have a lot of worksheets to print. Basically, I want everything to fit on one page per worksheet, just so I don't wind up with one or two rows on a page 2, but I don't want it to squeeze everything onto one sheet if it's going to make the font too small. Is there an easy way to do this that doesn't require running a macro for each worksheet? If not, what would the VBA be to run a macro for each worksheet, or even one macro for all worksheets in a range?

Thank you!

Gary


2016-06-02 17:36:27

Mary

I need to insert page break before the word Carton No on the excel spreadsheet as below. Is there any function that I can use so I can automatically add page break before certain word?

Carton No : 00093453420068573061
Weight : 2.00

Style
ZR1601013F
ZR1601013F
ZR1601013F
ZR1601013F



Carton No : 00093453420068573207
Weight : 2.00

Style
ZR1601202F
ZR1601202F
ZR1601202F
ZR1601202F


2016-06-02 17:18:46

Mary

@Deanna : probably you could use Pivot Table first before Subtotal feature.


2015-12-23 22:58:16

Deanna

This has the potential to really help me with a project I'm working on. Instead of inserting a page break each time the contents of a column change, I need to insert a new page break before each occupied cell (or after the last blank cell).

For example, A1 is Person 1, A2 is blank, A3 is blank, A4 is Person 2, A5 is blank, A6 is Person 3, etc. with varying numbers of blanks between persons.

I would like to be able to print the data associated with each person individually.

Please let me know if you have any solutions or if you need more information. Thank you!


2015-10-19 13:56:03

Eugen Sumindan

I have done some digging and discovered that HPageBreak, HPageBreaks, VPageBreak, VPageBreaks can be used to manage the page breaks in VBA. The suggested PageBreaks property of the Range object cannot be used anymore for VBA controlled page break management. At least not for MS Office 2010. Here is the modified code that does the job:

Sub PageBreak101()
Dim CellRange As Range
Dim TestCell As Range

nCells = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count)
Set CellRange = Range(ActiveSheet.UsedRange.Cells(2), ActiveSheet.UsedRange.Cells(nCells))
For Each TestCell In CellRange
If TestCell.Value <> TestCell.Offset(-1, 0).Value Then
ActiveSheet.HPageBreaks.Add ActiveSheet.Rows(TestCell.Row)
End If
Next TestCell
End Sub


2015-10-19 13:26:03

Eugen Sumindan

I receive the same error even at the first attempt to set the PageBreak property of the Range object. Has anything changed in MSOffice? I am using Excel 2010.


2014-12-02 09:51:00

Willy Vanhaelen

I don't see why in the macro the variable CellRange is declared, then set to Selection and finaly use it only once. These 3 lines:

Dim CellRange As Range
Set CellRange = Selection
For Each TestCell In CellRange

Can be replaced by this 1 line:

For Each TestCell In Selection

It's perhaps no big deal but it serves simplicity and readability.


2014-12-01 23:33:14

JohnB

Very VERY nice tip. I would suggest to leave the "Summary below data" box unchecked as, if checked, this creates an unnecessary line which shows that count is zero. Since we're really taking advantage of a ""side effect": of the subtotals routine, we don't need this summary line.


2013-10-03 05:34:42

Marakkar

I have found a Microsoft kb article which says Excel has limited the manual page break horizonally to 1026 ??? :(

http://support.microsoft.com/kb/284916

Regards,
Marakkar


2013-10-03 04:50:16

Marakkar

Excel 2010
Windows 2007

I have been using your VBA code in excel macro for sometime without any issue. All my use was to insert less than a 1000 of pagebreaks in a sheet.

Now I have worksheet of inventory of 8163 records that is for materials lised in 1132 boxes which need page breaks, i.e. each box will have less than 10 lines.

I have sorted the liste on box and selected the column applied the macro. Page break was inserted for 1023 changes of boxes and stopped to popup a Microsoft Visual Basic error message:
Runtime error '1004':
Unable to set the PageBreak property of Range class
with End / Debug button.

I even tried to select the remaining lines in the series still the same error appears. I am unable to debug and find the limitation in the code.

Kind help me to use this very valuble macro code.

Regards
Marakkar


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.