Print Title

Hi Schlic,

Normally you cannot use non-continguous rows as your Print Title setting. However, using a macro you can reorganize your data before printing (or print preview even) and then set it back the way it was once you've printed or previewed your sheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.ScreenUpdating = False
    With ActiveSheet
        .Range("5:5").EntireRow.Insert Shift:=xlDown
        .Range("11:11").Cut .Range("5:5")
        .Range("11:11").Delete Shift:=xlUp
        .PageSetup.PrintTitleRows = "$3:$5"
    End With
        Application.ScreenUpdating = True
        ActiveWindow.SelectedSheets.PrintPreview
        Application.ScreenUpdating = False
    With ActiveSheet
        .Range("11:11").EntireRow.Insert Shift:=xlDown
        .Range("5:5").Cut .Range("11:11")
        .Range("5:5").Delete Shift:=xlUp
    End With
    Application.ScreenUpdating = True
End Sub

This code inserts a new row 5, copies row 11 (was row 10 before the insert) to row 5 then deletes row 11 completely to close the gap. It then sets the Print Title group to rows 3-5 and previews or prints your sheet. It then reverses the insert/cut/delete sequence to put your data back to normal.

To insert this code, right-click on your sheet tab and choose 'View Code'. In the left hand column of the VB Editor window, double-click 'ThisWorkbook' and then paste the vba code shown above into the editor window at the right. Close the VB Editor and click the print or print preview buttons to see if it worked for you.