Dynamic Print Area and Page Breaks

[RedStarryGirl]

Hello,

I have a report in Excel 2007 which is just a pivot table controlled by a dropdown menu that users select a state from and it filters the pivot table. I already have code to dynamically set the print area for the report, as the number of rows varies dependent on the state selected. There are header rows repeated at the top, but what I need accomplished is to set a page break every 70 rows - not including the rows repeated at the top - so that a group of data is not split up. Is there a way to do this, given the Print Area? i.e. set a page break every 70 rows as long as the row is within the determined print area?

The code to set the print area:

Sub PrintArea()

Dim i As Integer
i = 14
Do While Cells(i, 24) <> ""
    i = i + 1
Loop


With ActiveSheet.PageSetup
        .PrintArea = "A14:Z" & i - 1
        .PrintTitleRows = "$4:$13"
        .PrintTitleColumns = ""
End With
End Sub

 

[pbornemeier]

This code will add a page break every 70 rows starting with row 84, but unless the paper is very large or the row height / font is small, the automatic page breaks will also appear.

Sub PrintArea()

    Dim i As Integer
    Dim lLastDataRow As Long
    Dim lX As Long
    
    lLastDataRow = Cells(Rows.Count, 24).End(xlUp).Row  'last filled cell in column X
    
    i = 14
    Do While Cells(i, 24) <> ""
        i = i + 1
    Loop
    
    
    With ActiveSheet.PageSetup
            .PrintArea = "A14:Z" & i - 1
            .PrintTitleRows = "$4:$13"
            .PrintTitleColumns = ""
    End With
    
    For lX = 14 + 70 To lLastDataRow Step 70
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(lX, 1)
    Next
    
End Sub

 

[RedStarryGirl]

Thank you for your help! I actually got some assistance from a colleague and ended up with the following code. I am very much a beginner with VBA so I don't know if there are advantages/disadvantages for using this method over yours as far as efficiency goes. P.S. I had to change my indicator column for finding the last row to print because there were some blanks in it.

Sub PrintArea()
Dim i As Integer
i = 14
Do While Cells(i, 9) <> ""
    i = i + 1
Loop
i = i + 30
Dim j As Integer
j = 84
Do While j < i - 1
Rows(j & ":" & j).Select
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
j = j + 70
Loop
With ActiveSheet.PageSetup
        .PrintArea = "A14:Z" & i - 1
        .PrintTitleRows = "$4:$13"
        .PrintTitleColumns = ""
End With
End Sub

Thanks again!