Friday, March 18, 2005

Printing repeating rows and columns in Excel

Excel has a great function to assist with printing large spreadsheets that are going to cover more than one sheet of paper.

It is often useful to be able to repeat the left column(s) and top row(s) on each page.
The way to do this is to go to File | Page Setup and click on the Sheet tab.
Now click in the Rows to repeat at top section and then highlight the actual rows in the spreadsheet (behind the dialog box) that you want to repeat at the top of every page. Do the same for the columns if you want columns to repeat at the left.

Its always a good thing to use the print preview to check this.

The VBA code for this is:

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = "$A:$B"
End With
This will repeat rows 1 & 2 and Columns A & B on each page printed.