How to Insert Page Break in Excel Between Rows

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.


Download Practice Workbook

You can download the practice workbook from here.


3 Easy Methods to Insert Page Break 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.

Insert Page Break Between Rows Manually

Insert Page Break Between Rows Manually

  • After that, you will get the below result at first.

Insert Page Break Between Rows Manually

  • Next, choose the desired row you want to insert the page break.

Insert Page Break Between Rows Manually

  • Afterward, go to the Page Layout option and select the Breaks option.
  • Then choose the Insert Page Break option to get the desired result.

Insert Page Break Between Rows Manually

  • Finally, you will get results like the below image.

Read More: How to Insert Page Break Between Rows 39 and 40 in Excel


2. Apply 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.

Subtotal Command to Insert Page Break

  • Furthermore, the Sort window will open on the screen.
  • Next, select the Sort by Name option and press OK.

Subtotal Command to Insert Page Break

  • 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 Use Page Break in Excel (7 Suitable Examples)


3. Using VBA Code to Add Page Break

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.

VBA Code to Insert Page Break

  • 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


Remove Page Break in Excel

After our printing is done for a certain report then we can remove the page break 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 (3 Easy Ways)


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.

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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo