Pagebreaks: Grouping rows together so print on same page

jeremy1026

New Member
Joined
Jan 12, 2007
Messages
17
Hi,

I have a report that shows 3 lines for each item. Is there a way to group sets of 3 rows together so they print on the same page so that it would insert a page break either before or after each set of 3 rows, not between them.

Thanks for any help!

[/img]
 
Try replacing that line with:

If Int(Val(Cells(r, 2).Value)) <> Int(Val(Cells(r - 1, 2).Value)) Then

Does this code give you an extra blank page at the end?

Are the values in sheet "OMS" column O text or numeric?
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Peter,

This time the macro worked!

It put a page break above every new route and the print area went down to row 500 (which is where the formulas in the spreadsheet go down to) so there were a few blank pages after the last data.

The values in the sheet OMS column O are numeric, it's what column B in this sheet is reading from.

Is it possible just to put a page break in when it needs one rather than after every change in column B. For example, there are 85 rows (plus the 8 header rows) to a page. In the data I am currently using the eighth group of rows overlaps the 85 rows from row 9 on the spreadsheet. Is it possible to put the page break at the top of this group and then start the new page from there and so on?

If I tag on the code I put in my original post onto the end of your code then it removes any blank pages from the end.

Thanks again for all your help.

Damian
 
Upvote 0
OK, another try. This should hopefully also solve the problem of the blank pages without additional code. It will fail if there are any very large blocks of data (more than one page) but you did say your blocks would not be greater than about 15 rows.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> InsertPageBreaks()
    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> lb <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> tb <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> lc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    lr = Columns("B").Find(What:="*", After:=Range("B1"), SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    lb = 9
    lc = 9
    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$8"
        .PrintArea = "$B$1:$AG$" & lr
        .Zoom = <SPAN style="color:#00007F">False</SPAN>
        .FitToPagesWide = 1
        .Fit<SPAN style="color:#00007F">To</SPAN>PagesTall = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    ActiveSheet.ResetAllPageBreaks
    <SPAN style="color:#00007F">For</SPAN> r = 10 To lr
        <SPAN style="color:#00007F">If</SPAN> Int(Cells(r, 2).Value) <> Int(Cells(r - 1, 2).Value) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> r - lb > 85 <SPAN style="color:#00007F">Then</SPAN>
                Rows(lc).PageBreak = xlPageBreakManual
                lb = lc
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            lc = r
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> r
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">If</SPAN> lr - lb > 85 <SPAN style="color:#00007F">Then</SPAN>
        Rows(lc).PageBreak = xlPageBreakManual
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Peter,

It sort of worked! The first page works fine(bottom of seventh set of rows - row 80), but then it goes awry. After the first page break the second one is 85 rows after the first, four rows into the 15th groups of rows (bottom of row 165). The third page break is then placed at the end of this group of rows (row 176). The fourth & fifth page breaks seem to be okay but the print area extends down to row 500 still.

Thanks

Damian
 
Upvote 0
Damian

I have edited my code above slightly..
Changed lb = r to lb = lc
Try that
 
Upvote 0
Peter,

Just the job. It still sets the print area to row 500 but I can sort that myself.

A million thanks.

Damian
 
Upvote 0
Peter,

Just the job. It still sets the print area to row 500 but I can sort that myself.

A million thanks.

Damian
No problem. :-D

The print area is being set OK in the sample sheet that you sent me. Check carefully that the "empty" cells below your data do not in fact have spaces or something in them. In a blank cell, try something like =LEN(B500)
If it returns something other than zero, then there is something in the cell and that will be why the print area is being set there.

Another thing to try..
In the code immediately below this line
lr = Columns("B").Find(What:="*", After:=Range("B1"), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
put a new line
MsgBox lr
and run the code. The message box should return the last row of data and then the line
.PrintArea = "$B$1:$AG$" & lr
should set the print area appropriately.

I am about to sign off for the night but will look tomorrow to see how you went.
 
Upvote 0
Peter,

I inserted the code as you suggested and the Message Box returned 500. Still, I have everything working as it should do so I'm happy anyway.

Thanks

Damian
 
Upvote 0
Peter,

I inserted the code as you suggested and the Message Box returned 500. Still, I have everything working as it should do so I'm happy anyway.

Thanks

Damian
Good, glad you have it going now.

As a matter of interest:
a) What does the formula =LEN(B500) return?
b) Assuming there is a formula in B500, can you copy it out of the formula bar and paste it here?
c) If that formula refers to a cell on sheet "OMS" what is in the cell(s) referred to?
 
Upvote 0
Peter,

a) 0
b) =IF(OMS!O505="","",OMS!O505)
c) There is nothing currently in the cell on the OMS sheet, but it could contain number like in column B on the DMS sheet. ie 122.01, 147.05 etc
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top