Print a block of data on a single page

[helplise]

Hi, Hoping someone can help me. I have a file that will vary in the number of rows it contains. I want it all to print onto the first page if possible, however if there are many rows of data going beyond the first page, I'd like the totals section to print on the next page. ie no page break between these particular rows. I can't just insert a page break as most of the time the whole block will fit on page 1 and we want as few pages printed as possible. We can't shrink to fit onto a single page as it will throw the format and be unreadable when there are many rows. Any ideas? Thanks so much

 

[drsarao]

I am presuming the Totals column is E and totals block is 6 rows deep (change as needed)
Try this:

Sub TotalBreak()
With ActiveSheet
    lrT = .Cells(.Rows.Count, "E").End(xlUp).Row 'Last row in Totals column [B]- presumed E [/B](change if needed)
    lPB = 0 'last natural page-break row
    If .HPageBreaks.Count > 0 Then 'find out if more than 1 page
        For Each hPB In .HPageBreaks
        lPB = hPB.Location.Row
        Next hPB
    End If
    'check if the Totals block (presumed 6 rows deep) is getting cut by a natural page break
     If lPB > (lrT - 5) And lPB < lrT Then 'insert page break at subtotal row
        .Rows(lrT - 5).PageBreak = xlPageBreakManual
    End If
    .PrintOut preview:=True 'print preview - set preview to false after testing
    If lPB > (lrT - 5) And lPB < lrT Then 'remove the page break (cleanup)
        .Rows(lrT - 5).PageBreak = xlPageBreakNone
    End If
End With
End Sub