1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Chirag R Raval, Aug 23, 2018.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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
  2. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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: Aug 24, 2018
  3. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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
  4. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
  5. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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...
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    Upload sample workbook please. Clearly indicating what end result you are after.
  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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
  8. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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

    Attached Files:

  9. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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.
    Chirag R Raval likes this.
  10. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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
  11. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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.
    Chirag R Raval likes this.
  12. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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
  13. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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.
    Chirag R Raval likes this.
  14. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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
  15. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Chirag R Raval
    This could work or not.
    Code (vb):

    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
     
    Chirag R Raval likes this.
  16. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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
  17. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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.
    Chirag R Raval likes this.
  18. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    Here's sample code to move last page break.
    Code (vb):
    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
    Chirag R Raval likes this.
  19. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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
  20. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    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.

    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.
    Chirag R Raval likes this.
  21. p45cal

    p45cal Well-Known Member

    Messages:
    1,243
    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 (vb):
    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.
    Chirag R Raval likes this.
  22. Chirag R Raval

    Chirag R Raval Member

    Messages:
    655
    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
  23. p45cal

    p45cal Well-Known Member

    Messages:
    1,243
    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 (vb):
    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.
    Chirag R Raval likes this.
  24. p45cal

    p45cal Well-Known Member

    Messages:
    1,243
    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 (vb):
    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
  25. p45cal

    p45cal Well-Known Member

    Messages:
    1,243
    …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 (vb):
    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

Share This Page