• 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

Dear Sir @p45cal,

Just Amazing, It is do as required ,

This is my bad , that from 1st post though point is strait forward as per yours “ 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.”

Don’t know Why I can not described well that what I actually requires, even i wrongly think/ believe that just drag/ shift/ move last page break to bottom of print area & set All Well, but i am wrong.I don't know about that actually Draging/moving/shifting mess up the print consistency by left odd & strange, ugly blank area on paper.

Hope Microsoft inherit your invaluable, tremendous helpful code must should be inherit with Excel's new version or as an update current version as very useful & cost effective feature.

Reason? , Just & just most people don't want last page with just 1,2,3 or 4 rows there, unfortunately, if they seen, on that moment their subconscious get shock they actually don't want this (just blank paper with only 4 rows) ..they accept that situation with little hesitation, all people feel this, & that every one knows. Most of them ready to accept less paper with all data in readable form.

Hesitation start from who generate printout.(now click in mind , that why Microsoft give page break drag facility, why give zoom scale facility, why they give facility to cover most data can print in minimum or limited pages)

Think I have 160 file to generate, about 120 files have last page with just 4 rows, then for 120 page printer’s every part do their effort , means life of printer parts reduce to 120 page from its life, ink life reduced, for Extra hard paper used, & many other aspect that we don’t know.

If we give print job to professional printer, they naturally take fixed rated price per page even page have only 1 line remain on 1 any page. Because whole resources in which he invest, used for that single page, in single run, On that moment , we realise that it should not happen.

We now can not say that Currently “120 page save or use is my single matter for which this thread generated” But in reality , this matter face by whole world ,on daily basis.

Microsoft should inherit this feature, by hand input , in text box , numbers of line on last page should covered in previous print area to reduce unnecessary page print.

Yes sir @p45cal, your code do its job without effect on structure, flow & neatness of data for print, (as per your quete ” 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.”

I also test with some rows hidden condition that also work perfectly, your “Areas.count” work perfectly.

Thank you very much All your expert’s invaluable help.

Sir @Chihiro - “Thank you for learn me even basics “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.” Some times our (user's) mind miss the basics in overwhelming condition when user's mind try to cover various aspect of matter of codding that affects each other. Thank you very much sir.

Sir @vletm - Without your effort on explore various aspect of page setup & print area, we cannot reach on final destination. as per your approach & try to "Fit to page tall" is very useful & become key factor. Thank you sir.


finally code end with

If orphanrowscount < 5 Then
.PageSetup.Zoom = False
.PageSetup.FitToPagesTall = hpbcount (number of horizontal page break)



Hartley wish & Hope this thread may have become more learn able , more explore on various aspect regarding page setup & print which become forever helpful to world.

Regards,
Chirag Raval
 
Last edited:
Dear Sir @p45cal,

I put to check first if active window is in pagebreak preview?
If not then go to pagebreak preview and at last before end its check
If active window is in pagebreak preview? If yes then go to
Normal view.

May be this prevent from "Subscription out of range"

I am right?

Please guide.

Regards,

Chirag Raval
 
I put to check first if active window is in pagebreak preview?
If not then go to pagebreak preview and at last before end its check
If active window is in pagebreak preview? If yes then go to
Normal view.
Normally, I wouldn't expect it to matter whether it was in pagebreak preview or not - it should just work.

May be this prevent from "Subscription out of range"
On which line does this happen?
If it's the same line as before then what value does hbpcount have at that time?
Otherwise I'd like to see the file that this is happening on.
 
Back
Top