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.

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.

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

1. Using Print Titles Command to Repeat Repeat Rows while Printing

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.

• 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.

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.

Read More: How to Repeat Rows at Top in Excel

2. Rename the Rows with Print_Titles (Which Are to Be Repeated While Printing) in the 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.

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

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 Repeat Rows at Top of Specific Pages in Excel

3. Applying VBA Code to Repeat Rows When Printing in Excel

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.

• As a result, the Microsoft Visual Basic for Applications window will appear.
• Now, from the VBAProject group choose Sheet 4.
• 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

• Afterward, press Ctrl + S.

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

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

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

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

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

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

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

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

Read More: How to Make a Pattern Repeat in Excel

Download Practice Workbook

You can download our practice workbook from here for free!

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. Thank you!

Related Articles

• How to Repeat Rows for a Specified Number of Times in Excel
• How to Repeat Rows in Excel at Bottom
• Repeat Text in Excel Automatically
• [Fixed] Repeat Last Action Not Working in Excel

<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel