2 Approaches to Print Borders at Page Break in Excel

If you are putting a page break in the middle of your data set, you might want to add a border around that page break so that it appears more professional. Also, it might be mandatory when you want to print a dataset containing a huge number of rows. You can print horizontal page breaks with borders in Excel by using either VBA code or the border format. Both of these options are available to you. This article will demonstrate 2 distinct methods of Excel print borders at page break.

Here is an overview of before and after inserting the border at the page break.

Before and after the process


📁 Download Excel File

Download the Excel file we used to create this article so you can practice.


How to Insert Page Break in Excel

Before we begin with the procedures of Excel print borders at Page Break, we want to give you an idea of how to insert page breaks in Excel worksheets. Because, unlike Microsoft Word and PowerPoint, Excel does not automatically put breaks in a sheet. So, if you want to know how to insert a page break in Excel, you have to follow the steps below.

 ⬇️⬇️ STEPS ⬇️⬇️

  • First, select a row. We want to insert a page break below this row.
  • After that, go to the Menu Bar and click on the Page Layout option.
  • Then, from the Page Setup group, click on the Breaks drop-down menu.
  • From there, select Insert Page Break.

Inserting page break in Excel worksheet

  • Then, you will see the inserted page breaks in your worksheet.

Page break is inserted in the worksheet


Learn to Print Borders at Page Break in Excel with These 2 Easy Approaches

Excel provides 2 straightforward methods for printing borders at the page break. These include adding page breaks manually and using VBA code to add borders. More information about these can be found in the subsequent sections of the article. Alright, let’s get started.

A dataset containing student information (Names and Student IDs) will be used throughout this piece. After inserting two-page breaks into our data, we will demonstrate how to print page borders.

Dataset for printing borders at page break

Approach

1. Insert Borders at Page Break Using VBA Code and Print Them

Excel’s Visual Basic for Applications (VBA) code has many implementations. In this piece, we will utilize this feature to add borders to our page breaks. Therefore, if you want to find out more, follow the steps.

 ⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the sheet name and right-click on it A Context Menu will open.
  • From the menu, select the View Code option.

Enter into Visual Basic for specific worksheet

  • As a result, a Microsoft Visual Basic window will open.
  • From there, you can see a window where you can paste the code. And this window is named after the sheet name. For example, it is named Sheet1 (VBA Code).

Paste VBA code

  • Then, paste the code that we have given below.
Sub Print_Border_at_PageBreak()

For Each PageBreak In HPageBreaks

With PageBreak.Location.Offset(-1, 0).EntireRow.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlThin

End With

Next

End Sub

🔨 Code Breakdown

👉  Here, Sub Print_Border_at_PageBreak() represents a subject procedure, and therefore we ended this using End Sub.

👉  Then, to find all horizontal page breaks we have used the For Each PageBreak In HPageBreaks.

👉  Now, to add a page border at each page break we used the line With PageBreak.Location.Offset(-1, 0).EntireRow.Borders(xlEdgeBottom). Also, this line break will be applicable for the entire row.

👉  We ended this loop with End With.

👉  .LineStyle = xlContinuous represents the line type. We wanted to insert a solid line. So, we used Continuous. You can use other options.

👉  We used .Weight = xlThin to determine the boldness of the line. We chose thin.

  • After that, you have to Run the code. To run the code, click on the Run symbol from the Visual Basic window.
  • Or, you can also press F5 from your keyboard to run the code.

Run VBA code

  • Also, you can run the code from the main sheet. Go to the Menu Bar, and from there click on the Developer option. Then from the Code option, go to the Macros. A dialogue box will pop up.

An alternative way of running the code

  • From there, choose the Macro Name and then press Run.
  • Finally, click on the File Tab.

See the print view

  • From, there go to the Print option.

Opening the print view

  • Alternatively, you can also open the print preview by pressing Ctrl+P from your keyboard.
  • And there you will see the borders at the page breaks.

Print preview after inserting border at page break

This is page 1. Look carefully that the border was inserted exactly at the page break.

Print view after inserting border at break 2

This is page 2, with a border at the page break.

Approach

2. Insert and Print Borders at Page Break Manually

If you are not comfortable with writing the VBA code you can also insert borders manually at the page break. It is a very simple method. But as like VBA code, this is not automated. You have to do it manually.

 ⬇️⬇️ STEPS ⬇️⬇️

  • First of all, select the last rows where the page break exists.
  • Then, go to Menu Bar, from there click on Home and you will see a group named Font.

Inserting border at page break

  • From there, click on a drop-down menu named Borders, and you can find various border options.
  • Select the Bottom Border option.

Inserting page break manually using bottom borders

  • Repeat this process for all the page breaks.
  • Finally, go to the Print view from the File tab. You can see that the borders are inserted at the page breaks.
  • Alternatively, you can also open the print preview by pressing Ctrl+P from your keyboard.

Print view after inserting border manually at break 1

This is the first page break, notice that the borders that we have inserted are displayed here.

Print view after inserting border manually at break 2

Same for the second page break and rest.

  • You can also insert the page breaks using Outside Borders.

Inserting page breaks manually by outside borders


📝 Takeaways from This Article

📌  You have learned how to use VBA code to insert borders at page breaks.

📌  Now, you can also insert borders manually using different borders.

📌  In addition, you have learned how to insert page breaks.


Conclusion

Border insertion at page breaks is a simple task. You should now have a fair overview of how to insert borders at age breaks, no matter how long the dataset is. Please leave a comment if you have any suggestions or questions. Don’t forget to visit our ExcelDen page to enhance your Excel-related knowledge.

(Visited 24 times, 1 visits today)
Lamisa Musharrat

Lamisa Musharrat

Hey there! I am Lamisa Musharrat, a Marine Engineer and a Content Developer. I love to help people by writing articles for them. Hope you will enjoy my blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo