This tutorial will demonstrate how to insert a page break in excel between rows. Page breaks are very helpful in case of breaking a worksheet into different portions for printing. It helps the viewer or the reader to understand the full report. Sometimes if a whole worksheet is put on just one page then it looks too messy and difficult to understand. So our goal is to learn how to insert page breaks in excel between rows.
Our goal is to insert a page break between rows in a certain worksheet. For that, we can use any of the three methods described below:
1. Insert Page Break Between Rows Manually in Excel
In our first method, we can insert a page break between rows manually. It is the quickest and easiest way to insert a page break. So, follow the below steps to learn this method.
Steps:
- First, select the proper dataset you want to use.
- Then, go to the View tab and select Page Break Preview option to have the full display preview.
- After that, you will get the below result at first.
- Next, choose the desired row you want to insert the page break.
- Afterward, go to the Page Layout option and select the Breaks option.
- Then choose the Insert Page Break option to get the desired result.
- Finally, you will get results like the below image.
Read More: How to Use Page Break in Excel
2. Apply Excel Subtotal Command to Insert Page Break
Our goal is to use Subtotal Command to insert a page break between rows in a excel worksheet. This process is very useful if a conditional page break is needed. The method can be done by following the below steps.
Steps:
- Initially, select the whole data table.
- Then, go to the Data tab and select the Sort option.
- Furthermore, the Sort window will open on the screen.
- Next, select the Sort by Name option and press OK.
- Afterward, go to the Subtotal option in the same Data tab as the previous step.
- After clicking OK, the Subtotal window will come onto the display.
- Then go to the ‘Add subtotal to’ option and tick the Name option.
- Subsequently, select the Page break between groups option and press OK.
- At last, you will get the result below.
Read More: How to Insert a Page Break in Excel
3. Use VBA Code to Add Page Break Between Rows
This time our goal is to add a page break in excel using VBA code. The process is a bit lengthy but if you are comfortable with codes then it is the best one. The steps are described below:
Steps:
- Firstly, press Alt+F11 to open the VBAÂ window.
- Secondly, press the Insert option and select Module to get the desired window.
- Thirdly, input the following VBA code
Sub InsertPageBreak() 'Declaring the data types of the variables'        Dim selectionedrange As Range        Dim currentCellvalue As Range  'Assigning value to the variables'        Set selectionedrange = Application.Selection.Columns(1).Cells        ActiveSheet.ResetAllPageBreaks  'Running the for loop'        For Each currentCellvalue In selectionedrange                If (currentCellvalue.Row > 1) Then                        If (currentCellvalue.Value <> currentCellvalue.Offset(-1, 0).Value) Then                             ActiveSheet.Rows(currentCellvalue.Row).PageBreak = _                                    xlPageBreakManual                        End If                End If        Next currentCellvalue End Sub
- Next, go to the Run option and select the Run Sub/UseForm F5 to run the VBA code.
- Lastly, you will get the result like the below image.
Read More: How to Insert Page Break Based on Cell Value with Excel VBA
How to Remove Page Break in Excel
After our printing is done for a certain report then we can remove the page break lines to use the worksheet comfortably. So our goal is to remove the page break in excel by following the below steps.
Steps:
- At first, select the table that already has page breaks inserted.
- Then, go to the Page Layout option and select the Breaks option.
- Afterward, choose the Reset All Page Breaks option.
- Finally, you will get the result below.
Read More: How to Remove Automatic Page Break in Excel
Things to Remember
- In the second method, the heading of each column must be marked. Otherwise, the Subtotal Command won’t work.
- In the case of using VBA code, it sometimes doesn’t work perfectly. So, we will recommend not to use it.
- In case of removing a page break, if you want just a one-page break then you should select the row and click on Remove Page Break option.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Henceforth, follow the above-described methods. Thus, you can learn how to insert a page break in excel between rows. Let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
