auto page break around cell range so as not to print a range across 2 pages

Hi everyone.

I have a sheet which I need to print out. Its contents is made up of about 30 different sections which change (by hiding cells with zero in them) so its length can increase or decrease depending on content. Normally the finished sheet prints out over 10 pages or so and so far I have been having to set page breaks manually (in page view) so that it prints correctly. Some of these sections are small enough to fit several on one page so the page break at SubTotal change is no good to me as I'll end up with 30 pages and I don't want to unnecessarily waste paper, save the planet and all that.

What I need to happen is for page breaks to occur every 27cm unless the proposed break is in the middle of a cell range and in that case should break just before that range (all my ranges start with the word "Range_"blah blah etc). I don't know if its possible to specify distance in cm but the problem with using row numbers is that some of my cells have wrapped text and can be as deep as 3 or 4 rows.

None of my current sections (at the moment) are over a page long but if they was then obviously they would have to split over 2 pages.

so in a non VBA language I reckon it'll go something like this:

insert page break every 27cm
else, check if in middle of range and if so break before "Range_"
else, if range is longer than 27cm break at 27cm

I'd appreciate any help with this as I've been scouring the internet for ages and I can't find anything that works.
See attached file for rough idea of what I've got to deal with.

Hi lonemascot.

You will probably get a faster and more accurate response if you upload a sample workbook that includes your 'Named Ranges'. We only need that one sheet. If the sheet references other items, make all the cells on the sheet to be values.
If the file is large, it can probably be uploaded as a zip file.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Lewis

Hi Lewis

ok i will have a go at that now. thanks.

Ok think I've done it now. I have only done the first half of the named ranges but there's probably enough to work with.

There are 2 sheets in the workbook. One shows the raw data and the other shows what I want to achieve after clicking the command button.

I really hope someone can help me out with this.

Hi,

Very nice design. I can see you put a lot of work into the SpreadSheet. This was a fun project to work on. Try the attached file, which should get you started. Please don't feel like you have to hurry to respond. I fully understand the time demands of work and family during the holiday season. In many cases I took a leap of faith and decided what you needed, which is quite often not what you want. I have very thick skin, and every feature is negotiable.

I tested the software, the best that I could, and fixed every unintended consequence that I could find. My speculation is that your fresh set of eyes, will find additional unintended features. Please et me know if you have any questions, problems, enhancement or un-enhancement requests.


Widows and Orphans Reference: https://en.wikipedia.org/wiki/Widows_and_orphans
Widow = A paragraph-ending line that falls at the beginning of the following page
Orphan = A paragraph-opening line that appears by itself at the bottom of page


Instructions for Use:
a. Save your file after making changes and before Paginating, as there is no UNDO after Pagination.
b. Left Click one of the 'Paginate' CommandButtons. Each CommandButton enables or disables one or more of the options below:
(1) Enable or disable the calculation of the Page Height in Excel UNITS (check Print Formatting).
This option is only required when an Excel 'Page Setup' option is changed that affects 'Page Height' (such as Top Margin, Bottom Margin, Header/Footer Margin).
(2) Display all rows or Hide All Rows containing a ZERO in Column 'A'.
c. Print Preview. The 'Print Preview' CommandButton will hide the DASHES in Column 'A'.
d. Left Click one or more of the other CommandButtons as required.
NOTE: A 'HIDDEN SECTION' is INVISIBLE after Pagination. To make it visible select the 'Display All Rows' Macro.


Data Integrity Check Warning:
The software checks to see if the 'Active Sheet' is a 'Quote Sheet' by looking at cells 'A4:C4' which MUST:
a. Be a 'Merged Area' containing cells 'A4:C4' only.
b. Be Font Size 16.
c. Have a Value of (CASE INSENSITIVE) 'ALL PRICES EXCLUDE VAT '.
If any of the above items change, Function IsActiveSheetAQuoteSheet() must be modified.


To Install the software in another file:
a. Make a backup copy of your file.
b. Open this file.
c. Presss ALT F11 to access VBA.
d. Press CTRL R to accces Project Explorer (if is not already visible).
e. Right Click the following modules and Export them (will not affect this file):
(1) ModPrettyPrint
(2) ModToolsAndUtilities
(3) ModThisWorkbook (NOTE: ModThisWorkbook NOT ThisWorkbook)
f. Close this file.
g. Open your file.
h. Presss ALT F11 to access VBA.
i. Press CTRL R to accces Project Explorer (if is not already visible).
j. Right Click any module from your file in Project Explorer and import the following modules:
(1) ModPrettyPrint
(2) ModToolsAndUtilities
(3) ModThisWorkbook
k. Cut and Paste all the Code(copy) from module ModThisWorkbook to the ThisWorkbook code module.
DO NOT delete the code in module ModThisWorkbook.
l. Save your file.
m. Install CommandButtons on the Active Sheet as follows:
(1) Press ALT F8 to display the Macro Menu.
(2) Run Macro aaaCreateQuoteSheetCommandButtons which will:
(a) Delete all CommandButtons created by this Macro. Other CommandButtons that need deletion, will have to be deleted manually.
(b) Create approximately 20 CommandButtons.
(c) Put 'Top' and 'Bottom' in Columns 'E' and 'F' once every 25 rows.
(d) Create a 'Yellow Merged Area' in cells 'E1' thru 'M7'.
NOTE: If for any reason the CommandButtons need to be deleted, run Macro aaaDeleteQuoteSheetCommandButtonsOnActiveSheet.
n. Software Installation completed. Save your file.


CommandButtons (Red):
a. Paginate and Hide Unused Rows
b. Paginate and Display All Rows
c. Paninate and Hide Unused Rows (Print Formatting - Not Checked)
d. Paginate and Display All Rows (Print Formatting - Not Checked)

Items that take a lot of time:
a. Hiding Rows
b. Print Setup Change (Header or Footer or Margin Change)


Software Development Tools:
a. Display All Rows
b. Hide Unused Rows
c. Hide Empty Rows Near the End of Data
d. Clear Manual Page Breaks
e. Clear Column 'D'
f. Display all areas of 'HIDE THIS SECTION'
g. Hide all areas of 'HIDE THIS SECTION'
h. Create Named Ranges that start with 'Range_'
i. Traverse Named Ranges - Output to Immediate Window (CTRL G in debugger)
j. Delete All Named Ranges
l. Delete Named Ranges that start with 'Range_'
k. Display Column 'D' Sentinel Count Status
l. Enable Macros
m. Disable Macros


Custom Document Properties:
The Following items are permanently stored in 'Custom Document Properties:
a. 'PageRowHeight' - contains the Last Calculated' 'Maximum Cumulative Row Height' allowed per page.
The Units are 'Excel Units'. There seem to be 75 'Excel Units' per inch.
This is used when the 'Maximum Cumulative Row Height' allowed per page is NOT obtained by the 'Paginate' Macro (user option).
b. 'ColumnDChangeTimeStamp' - contains a Date and Time Stamp to indicate when a Column 'D' value was last changed.
This is used by the 'Print Preview' Macro to determine if 'Page Formatting' is 'Out of Date'.
c. 'LastPrettyPrintTimeStamp' - contains a Date and Time Stamp to indicate when the 'Paginate' Macro was last run.
This is used by the 'Print Preview' Macro to determine if 'Page Formatting' is 'Out of Date'.


How the software works:
a. All Manual Page Breaks are cleared.
b. Delete all 'Continuation Rows'.
c. Hide or Display Unused Rows (that contain 0 [ZERO] in Column 'A') depending on the Input Option.
d. Hide Sections that have 'HIDE THIS SECTION' text in Column 'D'.
e. Hide Unused Rows near the 'Last Row Used'
f.Create a list of all the Named Ranges starting with 'Range_' (CASE INSENSITIVE) .
The list contains:
(1) Starting Row Number of the Range
(2) Ending Row Number of the Range
(3) Number of Non-ZERO Height Rows in the Range
(4) Range Name
g. Get the 'Maximum Cumulative Row Height' per page (either from a stored value or from a Calculation depending on the Input Option).
A temporary 'Scratch' Sheet is used to determine the 'Maximum Cumulative Row Height' allowed per page.
This allows different rows to be different heights. This step is needed only after 'Page Formatting (headers, footers, margins) are changed.
NOTE: Hidden rows have a height of ZERO.
h. Process one row at a time. 'Manual' Page Breaks (and sometimes 'Continuation Lines') are added
as required per the following scenarios:

(1) 'NEW PAGE' Sentinel - Set a 'Manual' Page Break Before the row containing a 'NEW PAGE' Sentinel (in Column 'D')

All the following scenarios occur when then Cumulative Row Height for the Page is greater or equal to the Maximum Allowed Row Height for the Page (i.e. 'Page is FULL'):

(2) and the number of rows remaining in the Range after this Page is smaller than the 'Minimum Range Row Height Required' for Continuation Lines:
(a) Set Manual (Horizontal) 'Page Break' before Row Number 'iPreviousNamedRangeEndRow + 1'
i.e. the Range must be in ONE PIECE (on the NEXT PAGE) - Widow Elimination (i.e. the Range must be in ONE PIECE (on the NEXT PAGE) - Widow Elimination).

(3) and the number of rows in the Range on this page is smaller than the 'Minimum Range Row Height Required' for Continuation Lines:
(a) Set Manual (Horizontal) 'Page Break' before Row Number 'iPreviousNamedRangeEndRow + 1' (i.e. the Range must be in ONE PIECE (on the NEXT PAGE) - Orphan Elimination).

(4) and the number of rows remaining in the Range after this Page is larger than the Widow and Orphan Elmination Threshhold':
(a) Set Manual (Horizontal) 'Page Break' before this Row.
(b) Add a Continuation Line before and after the 'Page Break' and break the 'Named Range' into two or more Sheets.

(5) and the row is NOT in a Range:
(a) Set Manual (Horizontal) 'Page Break' before this Row.


To change the look of final output:
a. Increase or decrease the 'Row Height' of a Blank Cell between Ranges.
b. Increase or decrease the 'Row Height' for each row in a range of rows.
c. Change 'Page Formatting' such as increasing or decreasing Top Margins (or Bottom Margins, or Header Margin or Footer Margin).


Features Added:
a. 'Double Click' Column 'D' on a row that contain Font Size 16 in a Merged cell to:
(1)Display a 'NEW PAGE' Sentinel or
(2) Display a 'HIDE THIS SECTION' Sentinel or
(3) Clear the Column 'D' Cell
b. 'Double Click' Column 'D' on any other row to:
(1)Display a 'NEW PAGE' Sentinel or
(2) Clear the Column 'D' Cell
c. 'Double Click' any cell in Column 'E' to go to the 'Top of the Sheet'.
d. 'Double Click' any cell in Column 'F' to go to the 'Last Row Used (columns 'A' thru 'C' of the Sheet)'.
e. 'Print Preview Command Button replaces single DASH '-' with a Non-Printable Character (ASCII 160).
When'Print Preview' is completed, the DASHES are restored.
f. Added optional 'Date Printed' on Right Footer' .
NOTE: Most features can be DISABLED, by setting a CONDTIONAL COMPILATION constant value to 'False'.

Lewis

Hi Lewis

I'm absolutely gobsmacked.

I've just downloaded the file and had a very quick look at what you've done and the sheer quantity of work you have put into this is astounding. I had no idea my problem was as complex as it was and I am so grateful for you taking the time to do this and then write out an instruction sheet also.

I'm very busy today so won't have a lot of time to have a play with what you've done but I just wanted to express my gratitude for the time being.

You sir, are a legend. Thank you very, very much.

I didn't realize how complex it was either, until I started working on it. Please take your time, and try it out whenever you get a chance.

Lewis

Hi,

Occasionally 'Named Ranges' misbehave and generate a Run Time Error 1004, probably due to my ignorance of the deep dark secrets of 'Named Ranges'. There are Global 'Named Ranges' associated withe the entire Workbook, and local 'Named Ranges' associated with just one Sheet. I probably defined the 'Named Ranges' that were defined with VBA as Global, when they should have been Local (one Sheet only).

To solve the problem, and alleviate my splitting 'Named Range' headache, I modified the code to not use 'Named Ranges' at all. The 'Named Ranges' you already have will do no harm.

To implement the changes in your file that already contains the code:
a. Make a backup copy of your file.
b. Open my file.
c. Presss ALT F11 to access VBA.
d. Press CTRL R to accces Project Explorer (if is not already visible).
e. Right Click the following modules and Export them (will not affect this file):
(1) ModPrettyPrint
(2) ModToolsAndUtilities
f. Close my file.
g. Open your file.
h. Press ALT F11 to access VBA.
i. Press CTRL R to accces Project Explorer (if is not already visible).
j. Right Click module 'ModPrettyPrint' and select 'Remove ..." to delete the Module.
k. Right Click module 'ModToolsAndUtilities' and select 'Remove ..." to delete the Module.
l. Right Click any module from your file in Project Explorer and import the following modules:
(1) ModPrettyPrint
(2) ModToolsAndUtilities
m. Software Installation completed. Save your file.


Small changes made in Module ModPrettyPrint Sub PaginateQuoteSheet() at approximately line 587:

Please Login or Register  to view this content.

Changes made in Module ModPrettyPrint (complete rewrite):

Please Login or Register  to view this content.

Changes made in Module ModToolsAndUtilities (complete rewrite):

Please Login or Register  to view this content.

I apologize for the inconvenience.

Lewis

Hi Lewis

Please don't apologise. You are providing convenience in oodles.

Right I've implemented those changes and whatever you tweaked in the code has worked an absolute treat. Thank you so much for your help, you've saved my sanity from early collapse.

Am I ok to just delete the other command buttons as I'll only use the "Paginate Hide Unused Rows" and "Paginate Display All Rows".

Is there a way to hide rows which have nothing in them as well "0"? In the "Notes" section in each range there is a row which usually has nothing in unless i've made additional notes. I'd like this row gone if empty.

Am I ok to just delete the other command buttons as I'll only use the "Paginate Hide Unused Rows" and "Paginate Display All Rows".

Yes. The CommandButtons are just tools that I used for easy access to features I needed when I was writing the software. You can delete anything you do not need or do not want.

I do recommend you keep my 'Print Preview' command button because it removes the DASHES in Column 'A' before Print Preview, and puts them back when 'Print Preview' is completed. Of course from Print Preview, if you like what you see, you can then Print.

Is there a way to hide rows which have nothing in them as well "0"? In the "Notes" section in each range there is a row which usually has nothing in unless i've made additional notes. I'd like this row gone if empty.

Be careful what you wish for, because there are often unwanted side effects. For example if the line before the 'Section Total' is BLANK and hidden, the 'Double Line' is lost, because the BLANK line has the 'Double Line' format at the bottom. That can be changed to have the 'Section Total' have a 'Double Line' above it.

Having said all that, it is relatively simple to hide blank lines (or any lines), as long as there is a set of rules for hiding the lines. For example, I could hide the entire 'Notes' section if it were blank.

If you want to proceed, I suggest you manually hide lines after Pagination, and look at them using Print Preview. If you want any changes, let me know and I will be happy to comply.

Lewis

Yes I am aware of the border issues. Had to deal with a few anomalies when i had my basic button on the original. I will have to go through and make sure all the borders relate to the correct cell.

Hiding the "notes" section if blank would be pretty good though if you wouldn't mind. I feel so cheeky asking when you have already done so much for me.

Hi,

It took a while, but I wanted to make sure I got everything as correct as I could. See the attached file.

Update Notes:
a. Added Software Development Tool Macro FormatBaselineLinesAboveNotesAndSectionTotal() to Format Lines Above NOTES and Above SECTION TOTAL and Below HEADERS.
b. Added Tool Macro FindLastRowUsedInColumnsAthruConQuoteSheet().
c. Added Hiding of Blank Notes Sections.
d. Added Hiding of Blank Lines including Blank Lines in Notes Sections.
e.Added temporary Single Lines above 'Notes' and Double Lines above 'Section Total / Not Priced' during use of 'Print Preview' CommandButton. Added temporary 'Single Lines' beneath each 16 font Red Header line.
The lines are temporary during 'Print Preview' for ease of removal and to guarantee that all the temporary lines are removed.

f. Notes Row 63 - Text cut off on screen - but Print Preview OK. It was probably always like that.
g. If there are too many blank rows at the end of the Quote, there is probably a rogue NON-BLANK line in the file after the alleged last line.

h. The only changes made were in the following code modules:
(1) ModPrettyPrint
(2) ModToolsAndUtilities
To update your existing file:
(1) Make a Backup Copy of your file.
(2) Export the 2 modules from my file.
(3) Delete the 2 modules from your file, then import them.
(4) Save your file.

Lewis

Hi Lewis

I do apologise for not getting on here to thank you before now. I've only just implemented your changes and they work a treat. I've yet to print one out so will see if the border lines show up correctly depending on what's been hidden. Thank you for doing this. I really do appreciate it.

It was my pleasure to help. Please let me know if you need anything else.

Hi Lewis

I've encountered an anomaly. When I paginate the worksheet the code you wrote hides all the rows that have 0 in them. However, I've just noticed that it's also hiding rows that might be 0.5 or 0.2. Any ideas why it's doing this?

Hi,

I ran a couple of tests that indicate that what you see is probably not an anomaly, but a feature of Excel.

a. Manual Test - On two different computers (old desktop, recent laptop), the minimum row height above ZERO I was allowed to set manually was 0.75 (1 pixel).

b. VBA Test - The row height could be set in 0.25 increments, with the minimum being 0.25. Anything less than 0.25 is treated as ZERO by Excel.

Please Login or Register  to view this content.

Hi Lewis

I think you've misunderstood what i meant. I'm not referring to the row height but the actual value in column A. each cell references another part of the workbook and usually returns a number equal to or greater than 1. The problem occurs when the value referenced is less than 1 ie. 0.5

the vba must be looking at the value in the cell and seeing that it starts with zero, it then hides it. However, should it not check if the value is blank or equal to zero?

I gave the correct answer to the wrong question.

I see what you mean. I incorrectly treated the values as (long) integer numbers, so 0.4 would be rounded to ZERO (aka NIL) and 0.6 would be rounded to 1. I was looking for an integer value of ZERO.

It is a one line fix that now treats the values as decimals (type Double). You can fix it yourself by going into Module ModPrettyPrint at approximately line 1586.

Please Login or Register  to view this content.

Translated into English:
CLng(sValueA) means convert the string in Column A to type Long Integer
CDbl(sValueA) means convert the string in Column A to type Double (which is a scientific notation decimal type good to 15 digits)
0# is Microsoft shorthand for 0.0 (which is done automatically, which I hate).

Converting strings (or numbers) to different types is known as casting.

Please let me know if you have any problems.

Lewis

Well that worked perfectly. I don't know how you know what you're doing. Even with your helpful translation it completely baffles me how you'd even know where in the the module you wrote that single line. There's 2336 lines of code in that module alone!!

Thanks for your speedy response.

Your welcome. I'm glad you caught the bug, hopefully before you shared bad information.

First of all I use a developer's tool Excel Add-In that has great search capabilities.

From the symptoms you described, it seemed like I was doing an integer comparison somewhere, where I should have been doing a decimal comparison. Next, I knew that the problem was in Column A. In your project, each time I read a value from Column 'A', I read it in as a string, and called it sValueA. When I did a search, I found 36 occurrences of sValueA in the whole project. Next, I found that sValueA was used in routine HideBlankRowsAndHideUnusedRows(). When I looked in that routine, I found the original line that you changed.

Before changing the code, I tested different ways to make sure the value was really ZERO, before I changed the code.

I commented out the original line, replaced it with the new line, and tested. I double checked to see if there was any place else I compared the value in Column 'A' to ZERO, and did not find any.

I had already sent out a few quotes but due to it only affecting smaller quantities I doubt anyone would even notice. It certainly wouldn't be a deal breaker lol.

In any case, i'm still impressed even if you are cheating and using Excel plugins