Dropping blank pages from Excel Spreadsheet's Printout

Hello everyone,

I decided to re-post my question since it was ignored the first time. Maybe I'll get lucky on second attempt. I just wonder, is this question so not interesting to the Forum members?

So, here I go again.
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 - could be multiple spreadsheets in a workbook - 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 pages depending on the number and width of columns in the spreadsheet, and we have to either manually reformat Excel files to get rid of blank pages, or just pull out, again manually, all these pages from paper 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 - hense, batch printing.

Thank you in advance.

If you define a print area for each sheet, then that can overcome your
problem if you only pick populated ranges to be in your print area.

Without knowing specifics about how your data is laid out, it would be hard
to offer any more specific.

one problem you probably have is that you have unused space being seen as
used by Excel. Look at Debra Dalgleish's information on correcting this
problem:

http://www.contextures.com/xlfaqApp.html#Unused

--
Regards,
Tom Ogilvy


"sgluzberg" <sgluzberg.1py82h_1117631116.3998@excelforum-nospam.com> wrote
in message news:sgluzberg.1py82h_1117631116.3998@excelforum-nospam.com...
>
> Hello everyone,
>
> I decided to re-post my question since it was ignored the first time.
> Maybe I'll get lucky on second attempt. I just wonder, is this question
> so not interesting to the Forum members?
>
> So, here I go again.
> 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 - could be
> multiple spreadsheets in a workbook - 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 pages depending on the number and width of columns in the
> spreadsheet, and we have to either manually reformat Excel files to get
> rid of blank pages, or just pull out, again manually, all these pages
> from paper 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 - hense, batch printing.
>
> Thank you in advance.
>
>
> --
> sgluzberg
> ------------------------------------------------------------------------
> sgluzberg's Profile:
http://www.excelforum.com/member.php...o&userid=23528
> View this thread: http://www.excelforum.com/showthread...hreadid=375598
>

Hello Tom,

Thanks for your reply.
My problem is that I don't know in advance how the data in spreadsheets will be laid out. We're getting 1000s of Excel files for conversion to TIFF images. All of them are just files without any specific layout. There's no patterns or templates.
So the solution must be as generic as possible because I don't think it would be a good idea to format - even programmatically - each worksheet of each workbook individually. It would take too much time, and time is an issue here. So, I think some print settings - scaling, margins etc. - should be the same for all of the files.
You're saying that I need to set a Print Area for spreadsheet and print only populated ranges to overcome my problem, but this is what I'm trying to figure out - how to print only populated ranges?

Thanks.

Sub PrintActiveWorkbook()
Dim sh As Worksheet
Dim rng As Range
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If Application.CountA(sh.Cells) <> 0 Then
Set rng = GetRealLastCell()
ActiveSheet.Range("A1", rng).PrintOut
'Debug.Print ActiveSheet.Range("A1", rng).Address(external:=True)
End If
Next
End Sub


Function GetRealLastCell() As Range
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Set GetRealLastCell = Cells(RealLastRow, RealLastColumn)
End Function



--
Regards,
Tom Ogilvy


"sgluzberg" <sgluzberg.1q05ig_1117721113.0682@excelforum-nospam.com> wrote
in message news:sgluzberg.1q05ig_1117721113.0682@excelforum-nospam.com...
>
> Hello Tom,
>
> Thanks for your reply.
> My problem is that I don't know in advance how the data in spreadsheets
> will be laid out. We're getting 1000s of Excel files for conversion to
> TIFF images. All of them are just files without any specific layout.
> There's no patterns or templates.
> So the solution must be as generic as possible because I don't think it
> would be a good idea to format - even programmatically - each worksheet
> of each workbook individually. It would take too much time, and time is
> an issue here. So, I think some print settings - scaling, margins etc. -
> should be the same for all of the files.
> You're saying that I need to set a Print Area for spreadsheet and print
> only populated ranges to overcome my problem, but this is what I'm
> trying to figure out - how to print only populated ranges?
>
> Thanks.
>
>
> --
> sgluzberg
> ------------------------------------------------------------------------
> sgluzberg's Profile:
http://www.excelforum.com/member.php...o&userid=23528
> View this thread: http://www.excelforum.com/showthread...hreadid=375598
>