How to Repeat Rows in Excel When Printing (3 Effective Ways)

Sometimes, we have to print large dataset which requires multiple pages to print. Now, when printing multiple pages, it is better if we can print the top header rows on every page. It would help people understand the prints better. Now, if you are looking for ways to do this task, you have landed in the perfect place. In this article, I will show you x effective methods to repeat rows in Excel when printing.


Download Practice Workbook

You can download our practice workbook from here for free!


3 Ways to Repeat Rows in Excel When Printing

Say, you have a dataset of 100 students’ marks in 3 subjects. Now, you want to print the mark sheet. Excel automatically prints the whole mark sheet in 3 pages. But, for the 2nd and 3rd pages, there is no header row. For which, it becomes difficult to understand after the printout. Now, you can follow any of the 3 ways below to repeat the header row in Excel when printing.

Sample Dataset to Repeat Rows in Excel when Printing

We have used the Office 365 version of Microsoft Excel here. You can use any other version that is available to you to avail your desired target. But, if you face any problems regarding versions, please leave us a comment below.


1. Use Print Titles Command

You can use the Page Setup dialogue box (which appears after pressing the Print Titles button) to repeat rows in Excel when printing. Go through the steps below to do this.

📌 Steps:

  • First and foremost, go to the Page Layout tab >> Print Titles tool.

Access to the Page Setup Dialogue Box

  • As a result, the Page Setup dialogue box will appear.
  • Subsequently, go to the Sheet tab from the dialogue box >> write $4:$4 in the Rows to repeat at top: text box >> click on the OK button.

Customize Page Setup Options to Repeat Rows in Excel when Printing

Consequently, you will see that now you will have your header row which is the 4th row on your dataset, being repeated on every page when printing. And, the prints would have a preview like this.

Repeated Rows in Excel When Printing

Read More: How to Select Column A as Titles to Repeat on Each Page


2. Rename the Rows with Print_Titles (Which Are to Be Repeated While Printing) in Name Box

Another quick trick to repeat rows in Excel when printing is to rename them with Print_Titles in the Name Box. Follow the steps below to accomplish your desired outcome in this way.

📌 Steps:

  • At the very beginning, click on the row header number of your header row (4 here) that you want to repeat on every page. If you have multiple rows to show on every page-top, then select all of them.

Click on Row Header

  • Afterward, click on the Name Box on the left corner of the formula bar.
  • Now, write Print_Titles in the Name Box area.

Print_Titles must be used to rename. Otherwise, you will not get desired output.

Rename Name Box to Repeat Rows in Excel When Printing

Thus, your task is accomplished. Now, if you print the pages, you will see that every page has the 4th row repeating on top.

Read More: How to Set Print Titles to Repeat in Excel (2 Examples)


Similar Readings


3. Apply a VBA Code to Repeat Rows When Printing

Moreover, you can apply a VBA code to repeat rows in Excel when printing. Follow the steps below to achieve your result in this way.

📌 Steps:

  • At the very beginning, go to the Developer tab >> Visual Basic tool.

Access to the Visual Basic Tool

  • Asa result, the Microsoft Visual Basic for Applications window will appear.
  • Now, from the VBAProject group choose the Sheet4.
  • Subsequently, write the following code in the code window of Sheet4.
Sub RepeatRowsPrint()
Dim NumberOfPages As Long
NumberOfPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
With ActiveSheet.PageSetup
.PrintTitleRows = "$4:$4"
ActiveSheet.PrintOut From:=1, To:=NumberOfPages
End With
End Sub

Write Code in the VBA Window t Repeat Rows in Excel When Printing

  • Afterward, press Ctrl + S.

Save VBA Code

  • Now, close the VBA window and go to the File tab from the main Excel ribbon.

Access the File Tab

  • Subsequently, choose the Save As option from the expanded File tab.

Choose Save As Option

  • As a result, the Excel Save As window will appear. Click on the Browse option.

Excel Save As Window

  • Consequently, the Save As dialogue box will appear.
  • Following, choose the Save as type: option as .xlsm format.
  • Finally, click on the Save button.

Save the Excel as .xlsm Format

  • Now, go to the Developer tab >> Visual Basic tool again.

Access the Visual Basic Tool

  • Now, click on the Run icon button for the Sheet4 code.

Run the VBA Code

  • As a result, the Macros window will appear.
  • Subsequently, choose the macro you created and click on the Run button.

Run the Macro

And thus, you will be able to print the pages with the repeated header row.

Read More: How to Repeat Formula for Each Row in Excel (7 Easy Ways)


Conclusion

So, I have shown you the most effective ways to repeat rows in Excel when printing. Go through the full article carefully to understand it better and apply it afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo