Lamisa Musharrat
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.
📁 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.
- Then, you will see the inserted page breaks in your 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.
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.
- 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).
- 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.
- 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.
- From there, choose the Macro Name and then press Run.
- Finally, click on the File Tab.
- From, there go to the Print option.
- 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.
This is page 1. Look carefully that the border was inserted exactly at the page break.
This is page 2, with a border at the page break.
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.
- From there, click on a drop-down menu named Borders, and you can find various border options.
- Select the Bottom Border option.
- 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.
This is the first page break, notice that the borders that we have inserted are displayed here.
Same for the second page break and rest.
- You can also insert the page breaks using 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.
