# Print a block of data on a single page

#### helplise

##### New Member
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
3.7 KB · Views: 4

### 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
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``````

Replies
1
Views
376
Replies
0
Views
326
Replies
1
Views
133
Replies
1
Views
95
Replies
0
Views
130

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.

### Which adblocker are you using?

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

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