Dropping blank pages from Excel Spreadsheet's Printout

[sgluzberg]

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.

 

[Tom Ogilvy]

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

 

[sgluzberg]

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.

 

[Tom Ogilvy]

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