VBA Printing and Page Breaks

[HappyCamper]

I'm having a tough time figuring out a way to solve this problem. This is what I have so far:

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$3"
.PrintTitleColumns = ""
.FitToPagesTall = 400
.FitToPagesWide = 1
.Zoom = False
End With
ActiveSheet.PageSetup.PrintArea = Range("a4:ew" & [a405].End(xlUp).Row).Address
Orientation = xlLandscape

What I have is a varible length spreadsheet that only needs to print data that is filled in. The width varies by users, but I only want it to print one page wide and always on legal paper. Also the rows need to be grouped by two. Basically what happens is that if someone has a different sized column the page break will automatically split one of the rows that is grouped by two. Other times it is not. I was trying to determine the page breaks set up by the above code and if it falls below an even row to move the page break up by one row. Any help would help me save the rest of my hair.

 

[BrianB]

Something like this :-

Sub AddPageBreaks()
    Dim LastRow As Long
    Dim MyPageLength As Integer
    '---------------------------------------------
    '- remove any existing page breaks
    ActiveSheet.ResetAllPageBreaks
    '- add page breaks
    MyPageLength = 70  ' number of rows to page
    LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
    '- main loop
    For r = MyPageLength To LastRow Step MyPageLength
        With ActiveSheet
            .HPageBreaks.Add Before:=.Cells(r, 1)
        End With
    Next
    MsgBox ("Done")
End Sub

 

[HappyCamper]

BrianB,

Thanks for the help. Unfortunately it did not do what I was looking for. Is there a way to determine where the page breaks are set up? I was thinking about fiting the info to one page wide and then checking the page break locations. Then based on the location move that page break up on row if it splits on of my data sets.

Thanks,
Chris

 

[BrianB]

I assumed that your data would be consistent so that pages would be the same length. This would be the best setup.

It is possible to check where existing pagebreaks are, but we cannot "move" them. Only put in a hard break. As you can see, there is more control if we make all breaks hard.

How can we ascertain if the page break is in the right place ? What does the data look like ?