Print a block of data on a single page

helplise

New Member
Joined
Jul 30, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • MrXcelPrintBlock.PNG
    MrXcelPrintBlock.PNG
    3.7 KB · Views: 4

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
I am presuming the Totals column is E and totals block is 6 rows deep (change as needed)
Try this:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top