• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shift last Page break to end of data if 4 rows remains after last page break

C

Chirag R Raval

Member
Dear All Experts,

Requirement occur to save print pages.

if 1 file contain total 3 pages & 3rd page have only 4 lines with subtotal or anything,
should be cover that data in 2 pages (its okay if scale of print little reduce) so we can save from print 3rd page which unnecessarily have most blank area.

we can move that page break by drag handle, but when many files programmatically generating , then after manually change page break in every file that need many efforts & time.

Need little help for shift or move or Shift last page break to the end of data if
there are 4 rows after that page break- in other words , if from last row of data,
page break above last row , is above 4 lines, then shift that page break at the end of that
data .

Help always be appreciated, because knowledge (Knowledge for good) is always respectable & honourable.

Regards,

Chirag Raval
 
Dear All,

Just Require VBA solution that

if there are 1 to 4 rows below last page break of last page ,

drag/move/shift that last page break to bottom till print area's end

we can say " merge that last automatic page break created by excel based of page setup , with print area's bottom end (may be data end) " though as a cost reduce print scale .

Is there possibility that after merge last page in above print area ,excel will reset- recreate or change its previously created automatic page breaks in whole print area to adjust print scale with page setup? or just reduce last page's print scale?

I studied 2 links of this site
https://chandoo.org/forum/threads/moving-a-horizontal-page-break.38667/#post-231639

https://chandoo.org/forum/threads/i...-subtotal-page-break-section.7111/#post-40867

but can not create or modify that for my solution.


please guide & help will be appreciated.

Regards,

Chirag Raval
 
Last edited:
Dear all Respected Experts,

Is there any solution about if last page have only 1 to 4 lines & as a print & page related viewpoint,
merge that lines in its above print area As we just manually drag and merge last page break at bottom
Print area?

Regards,

Chirag Raval
 
Chirag R Raval
Do that 2nd page enough space for those max 4 lines?
Have You tried to set 1st and 2nd PageBreak by code?
... and with 2nd PageBreak, check if Your '3rd page' max 4 lines can include to 2nd page...
 
Dear Sirs @veletm & @Chihiro ,

Thanks both for take interest.
@vletm Sir,

(1) Yes of course, because I am ready to accept reduction
Of whole print area's print scale (not only reduce 2nd page),that automatically adjust by excel.
I know that its achieve only after effects on
Readability but that also known that if just 4 lines merge in Pervious print area ,there are acceptable effects on readability.
( please adjust A4 paper's 4 side"s margin as left & right 0.3, Top-0.5, Bottom-0.3 inches
through vba before run pagebreak code)

(2) No, page breaks auto create by excel .

(3) answers as point no 1

Sir @Chihiro,

I will upload original in 1sheet &required in 2nd sheet.

Hope I described well.

Regards,

Chirag Raval
 
Dear Sirs,

Sample Workbook Attached

also attached screen shot of original file condition as sheet no 1 and after cove 3 pages data in 2 pages as sheet no 2 (required).

Screen Shot 1

BREAK-1.png


Screen Shot 2

BREAK-2.png


Screen Shot 3 (reduction in print scale)
PRINT SCAL REDUCED.png

I have already created about 160 files through VBA
now i want to update that vba code to add this function of check page break & adjust.
i will re generate that files through code with updation.

Hope there are some solution found

Regards,

CHirag Raval
 

Attachments

  • 11353 -ABC YOUR BOOKED W18 ORDER .xlsx
    27.9 KB · Views: 8
Chirag R Raval
Should Your printout look same every time?
If You would use zoom with this then printouts would look different!
(Same feature (much more 'bad') would get if You'll use 'Fit to'-option >>
You could get all to fit in even one page).
Do You print to both sides of paper?
That would also save paper - a lot.
 
Dear Sir @vletm,

I don't use zoom or manual scale or don't change print scale(print zoom) or not use 'Fit To' option via code or via manually .

i just manually drag last page break to bottom (on print area's bottom border) if there are 1 ,2,3 or 4 lines remains on last page before print so excel auto adjust its page break (some time, i change scaling for print look little big , but scale, till strictly should not drop any 1,2,3 or 4 lines on last page)

Customer's requirement is only one side printing for their convince.

Hope i described well.

Regards,

Chirag Raval
 
Chirag R Raval
Good, no scaling, no fit to page [and double side printing]!
Those means that layout would keep same.

If there are no space for even one row in the end of the 2nd page
then ... there is no space.
Of course, You would like to have two top rows in every page.
If not, then there would have one-two lines extra for 2nd page.

Do You need to use A4-size paper?
eg 'US Legal' would let print this to 2 pages.
 
Dear Sir @vletm,

Yes. top 2 rows as header , should repeat on every page.

for your point

" If there are no space for even one row in the end of the 2nd page
then ... there is no space."

so there are must be compromised with print scale to cover 3 pages' data in 2 pages. (interesting point is, i have just question in my mind that , in this situation,on which base excel adjust page breaks? due to some times (you can see in my attached file, after cover data in 2 pages, in print preview) first page have set as unstructured compare to 2nd page, can this controllable?)

Yes world accepted, standard A4 size paper .

in this situation, ideally, with which aspect of page setup, page break, user should compromise? Neat structured printing? Readability? or whats?

Hope there are some way found.

Regards,

Chirag Raval
 
Chirag R Raval
If 'no scaling, no fit to page' then

for me, it's like, if try to add to 1 ltr cup ... 1.5 ltr water ... hmm?
Those page breaks can set by code ... but it would help sometimes or not
... something same as You have noticed.

In some other cases,
You would ask to book more item (rows),
that there would be enough rows for every page ... or ..
every single page of paper would charge separate from Customer.
 
Dear Sir @vletm,

My simple requirement is How can we do by code Which we manually do Drag/Move/shift, last page break to bottom of print area? its okay if, reduce in print scale /print zoom that i already said till my first post & i already accept to reduction in scale.

please refer my print preview 2 screen shots.

(1) Before Drag/Shift/Move last page break
before shift page break.png

(2) After Drag/Move/Shift page break to Bottom print area.

after scale.png

you can see not major affects on readability I And all should accept this.

whole thread is just not require last page with just 4 lines, though any number of pages in print area, but last should not have only 4 lines.

Hope my 3rd try will be explore some way to solution.

Regards,

Chirag Raval
 
Chirag R Raval
This could work or not.
Code: 
Sub Force_It()
    With Sheets("original")
       .ResetAllPageBreaks
       With .PageSetup
            .Zoom = False
            .FitToPagesTall = 2
            .FitToPagesWide = 1
        End With
       .Rows(55).PageBreak = xlPageBreakManual
       .Rows(110).PageBreak = xlPageBreakManual
    End With
End Sub
 
Dear Sir @vletm,

If this macro is for just 3 pages & hard coded on particular rows (why need to work on particular ow numbers?), that seem ok..but when i run on 9 pages file, that last page only 1 lines, all data covers in just 2 pages, means we must also
provide on how many pages in which all data should cover...then its is better to open each 160 files , check each & set page break manually.

logically code is like below,

check last page break, if after that page break, rows 1 to 4 found, then shift/move/drag that last page break(which auto created by excel) on its current location to last line of data row location.

hope you can understand.

Regards,

CHirag Raval
 
Chirag R Raval
I hope that You could understand also this logically code ...
the 1st, You would know how many rows would printout per page? okay?
Next, You could make a loop which would check
when needed to set manually PageBreak?
You can set those manually PageBreaks to needed row
no matter how many rows/pages there would be.

> sometimes would need to test other logic ... sometimes not <

That code works which Your sample file and
with previous modifications
You could make it work with any number of rows.
It would be better
if You set those PageBreaks than both, Excel and You.

Why work with numbers ... ?
because number of rows (and those rows heights) also matters then PageBreak needs.
 
Here's sample code to move last page break.
Code: 
Sub changePageBreaks()
Dim lHBreak As Long
Dim lHBRow As Long
Dim lRow As Long
With ActiveSheet
    lHBreak = .HPageBreaks.Count
    lHBRow = .HPageBreaks(lHBreak).Location.Row
    lRow = .UsedRange.Rows.Count
    If (lRow - lHBRow) < 5 Then
        .HPageBreaks(lHBreak).DragOff Direction:=xlDown, RegionIndex:=1
    End If
End With

End Sub
 
Dear Sir, @Chihiro

Thank you very much, Just Perfect, just as per required.

(Also thanks for @vletm sir, for co-operate.)

Oh, very sorry, as first rule here, i make big mistake by not to try just record a macro , i test by record macro & get below ......

"ActiveSheet.HPageBreaks(8).DragOff Direction:=xlDown, RegionIndex:=1"

but i cannot set logic of last page break & compare with rows count for that thank you very much , provided by you.

something new to learn here, Dragoff & Region index, (is region index is print area's bottom border?)

After run your code on 9 pages file that's last line (row) after last page break is only 1, & in print preview, page auto scale ratio is 99, it seem last page's previous page affected by much lost of print structure flow but as per previously agreed, we must compromise some where or with some other aspect of page breaks, page setup & print preview.

But...But....if i just added 4 lines in that file before last page break & run your code, result of page no 7 & 8 is below.

Page 7
page 7.png

page 8 (now become last)
page 8.png

Page 1 to 6 are perfect,

i am now really tired, can there are some modification possible to adjust page 7's blank area adjust on every page ? it seems want to save page,cost of pay is high as much lost of consistency.

Regards,

Chirag Raval
 
Since I don't know the full scope of requirement. I can help you with code to execute your logic, but not build logic for you.

Uploading sample page where code does not produce desired result would be helpful.

is region index is print area's bottom border?
Click to expand...

No, region index is index of contiguous print area. Since, all your print area is one contiguous range, you'll only have 1. If you have multiple non-contiguous print area, then each area is assigned index based on it's position.
 
A slightly different approach.
This macro tries to look at how many excel rows would be printed on the last page. If there are fewer than 5 rows then it will adjust the page setup to fit to the-number-of-pages-that-would-normally-printed -1.
Code: 
Sub blah()
With ActiveSheet
  .PageSetup.Zoom = 100
  .ResetAllPageBreaks
  .UsedRange    ' reset usedrange
  hpbcount = .HPageBreaks.Count
  If .PageSetup.PrintArea = "" Then 'the printArea's never been set:
    Set printrange = .UsedRange
  Else 'a print area has been set:
    Set printrange = .Range(.PageSetup.PrintArea)
  End If
  orphanrowscount = printrange.Row + printrange.Rows.Count - .HPageBreaks(hpbcount).Location.Row
  If orphanrowscount < 5 Then
    .PageSetup.Zoom = False
    .PageSetup.FitToPagesTall = hpbcount
  End If
End With
End Sub
The effect is to scale the entire printout to fully fill all paper sheets if there would have been up to 4 rows by themselves on the last page.
It operates on the active sheet.
 
Dear sir @p45cal,

Great, I think , till now, you are very close to the core matter of this thread's requirement, i run your code, but...

subscription.png

I try to put (.) before , but Error 438" "Object Does not support this property or method" displayed

printrange.Row + printrange.Rows.Count - .HPageBreaks(hpbcount).Location.Row

Can not figure out problem.

Please help

Regards,
Chirag Raval
 
The only part of that line which has a subscript is
.HPageBreaks(hpbcount).Location.Row
and the subscript is hpbcount.
What is its value when you click Debug?
It could be 0 because there's only one page to print anyway - I hadn't coded for that (stupid me). See tweaked code:
Code: 
Sub blah()
With ActiveSheet
  .PageSetup.Zoom = 100
  .ResetAllPageBreaks
  .UsedRange    ' reset usedrange
  hpbcount = .HPageBreaks.Count
  If hpbcount > 0 Then
    If .PageSetup.PrintArea = "" Then    'the printArea's never been set:
      Set printrange = .UsedRange
    Else    'a print are has been set:
      Set printrange = .Range(.PageSetup.PrintArea)
    End If
    orphanrowscount = printrange.Row + printrange.Rows.Count - .HPageBreaks(hpbcount).Location.Row
    If orphanrowscount < 5 Then
      .PageSetup.Zoom = False
      .PageSetup.FitToPagesTall = hpbcount
    End If
  End If
End With
End Sub
However there's also a known bug with .HPageBreaks().Location which may be the cause:
https://support.microsoft.com/en-us...ut-of-range-error-message-when-you-use-hpageb
but don't be in too much of a hurry to ascribe the fault to this.

Perhaps when this happens, save the current file just as it is and attach here.
 
I also note that my count of rows on the last page before fitting to n pages (orphanrowscount) is badly wrong when you've chosen to hide rows, either manually or using the outline +/- buttons.
This variant hopefully addresses that too. There's a bunch of commented-out code which are the steps I used to reach the new orphanrowscount; I'll leave these in for the moment to allow easier debugging should things go wrong! Otherwise, they can be deleted.
Code: 
Sub blah()
With ActiveSheet
  .PageSetup.Zoom = 100
  .ResetAllPageBreaks
  .UsedRange    ' reset usedrange
  hpbcount = .HPageBreaks.Count
  If hpbcount > 0 Then
    If .PageSetup.PrintArea = "" Then    'the printArea's never been set:
      Set printrange = .UsedRange
    Else    'a print area has been set:
      Set printrange = .Range(.PageSetup.PrintArea)
    End If
    Set printrangevisible = printrange.Columns(1).SpecialCells(xlCellTypeVisible)
    Set LastAreaToPrint = printrangevisible.Areas(printrangevisible.Areas.Count)
    '    Set LastCellToPrint = LastAreaToPrint.Cells(LastAreaToPrint.Cells.Count)
    '    Set LastPageRange = Range(.HPageBreaks(hpbcount).Location, LastCellToPrint)
    '    orphanrowscount = LastPageRange.SpecialCells(xlCellTypeVisible).Cells.Count
    '    orphanrowscount = Range(.HPageBreaks(hpbcount).Location, LastCellToPrint).SpecialCells(xlCellTypeVisible).Cells.Count
    orphanrowscount = Range(.HPageBreaks(hpbcount).Location, LastAreaToPrint.Cells(LastAreaToPrint.Cells.Count)).SpecialCells(xlCellTypeVisible).Cells.Count
    If orphanrowscount < 5 Then
      .PageSetup.Zoom = False
      .PageSetup.FitToPagesTall = hpbcount
    End If
  End If
End With
End Sub
 
p45cal said:
to allow easier debugging should things go wrong!
Click to expand...
…and of course, with further testing, they did.
When SpecialCells(xlCellTypeVisible) is used on one cell it actually does it for the whole sheet so added an If statement to handle that. Next version of the code:
Code: 
Sub blah()
With ActiveSheet
  .PageSetup.Zoom = 100
  .ResetAllPageBreaks
  .UsedRange    ' reset usedrange
  hpbcount = .HPageBreaks.Count
  If hpbcount > 0 Then
    If .PageSetup.PrintArea = "" Then    'the printArea's never been set:
      Set printrange = .UsedRange
    Else    'a printArea has been set:
      Set printrange = .Range(.PageSetup.PrintArea)
    End If
    Set printrangevisible = printrange.Columns(1).SpecialCells(xlCellTypeVisible)
    Set LastAreaToPrint = printrangevisible.Areas(printrangevisible.Areas.Count)
    'Set LastCellToPrint = LastAreaToPrint.Cells(LastAreaToPrint.Cells.Count)
    'Set LastPageRange = Range(.HPageBreaks(hpbcount).Location, LastCellToPrint)
    Set LastPageRange = Range(.HPageBreaks(hpbcount).Location, LastAreaToPrint.Cells(LastAreaToPrint.Cells.Count))
    If LastPageRange.Cells.Count = 1 Then
      orphanrowscount = 1
    Else
      orphanrowscount = LastPageRange.SpecialCells(xlCellTypeVisible).Cells.Count
      'orphanrowscount = Range(.HPageBreaks(hpbcount).Location, LastCellToPrint).SpecialCells(xlCellTypeVisible).Cells.Count
      'orphanrowscount = Range(.HPageBreaks(hpbcount).Location, LastAreaToPrint.Cells(LastAreaToPrint.Cells.Count)).SpecialCells(xlCellTypeVisible).Cells.Count
    End If
    If orphanrowscount < 5 Then
      .PageSetup.Zoom = False
      .PageSetup.FitToPagesTall = hpbcount
    End If
  End If
End With
End Sub
 
You must log in or register to reply here.
Back
Top