Dropping blank pages from Excel Spreadsheet's Printout

[sglab]

Hello everyone,

I hope that somebody has had experience with what I have to accomplish. I'd really appreciate any ideas and tips on that. Code samples - dream come true.
So here goes. After applying specific page settings to spreadsheet before printing, it gets broken down into certain amount of pages - or print areas - and some of these could contain nothing but grid lines and columns' headers. So they essentially are blank. And sometimes there could be as many as 100s of these depending on the size of Excel file, and we have to either manually reformat specific Excel file for printing or just pull out, again manually, all these pages from printout, which is very annoying and time-consuming.
Is there a way to programmatically find these blank pages and exclude them from printing? We're talking about processing of 100s of Excel files at a time.

Thank you in advance.

 

[Ascentient]

I don't know if the excel side of this has it, but check to see if there is a "noData" event that could be setup on a per page basis, (maybe a detail section?)

Hope it gives you some kind of help.

Ascent

 

[sglab]

Ascentient,

Thank you for your reply. I'll look into it, but I'm pretty sure that there's no such event. I was thinking more along the lines of using somehow horizontal and vertical page breaks, that Excel creates in Print Preview and could be accessed through Excel Object Model, in order to get these blank pages. I just got lost in all these objects and properties, so I thought maybe somebody in the Forum has played with it.

Thanks again.

 

[Ascentient]

I have not played with things like that before, but thought maybe some suggestions would prod you in the right direction.

Something else that might help you:
If your pages are set to 'exact' length's you could test a cell to see if a value exists in it. If it does not advance to the next page.

I do not know how to do the advancement (maybe search for the next page break?) but it might be another option to explore.

 

[sglab]

I thought about approach like that too, but we're talking workbooks with multiple worksheets and with 30 to 40 columns and 10s of thousands of rows in each one of them. So I guess checking for the non-existant values on cell-by-cell row-by-row basis could take a substantial amount of time. Well, it's more of a question, than a statement.

Thank you.

 

[Ascentient]

Well, where I was heading with that is as follows with an example.

I created an excel sheet to track the finances of my bowling league. All my teams are on one excel sheet.

The first 38 lines are setup to control line fees due to the bowling alley, league dues, and columns setup for tracking totals of each week of the league.

Team 1 starts on Row 40 of the worksheet and extends to Row76.

Team 2 starts on Row 80 and extends to row 116

Team 3 starts on Row 120 and extends to 156.

etc, etc.

As you can see I have a 'fixed' number of lines that can be printed per page when I need to give a quartly report for the league. If you could write a routine or function to cross check a cell in lets say the first week of the league to see if there is a value. If there was you would know to print that section, if not then you would skip to the next section. (now in my instance I would not skip a section, but you get my point.)

 

[sglab]

Yes, I got the idea. I'll think of something.

Thanks for your help.