Check for empty cells in Print Area?

[Ed]

Is there a way to check the Print Area for empty cells (or at least no
visible value) so I don't print blank sheets?

Ed

 

[Tom Ogilvy]

Did you manually set the print area, or do you mean if you don't set the
print area and just print the page.

In any event, do you mean check it with a macro or what?

 

[Ed]

I'm wanting to print using a macro. Usually, all I adjust in the print area
is the margins. I then do a Print Preview and see what I've got. Many
times, the creator has modified a previous workbook, or copied a previous
worksheet as a "Template", and there may be a piece of border or an unused
formula that creates a blank page in the Print Preview.

I'm wondering if there's a way to "capture" the range shown in a Print
Preview and check it for cells with no visible value. In essence, do by
macro what I would visually and manually - if I can't see anything, don't
print that page.

Ed

 

[David McRitchie]

Hi Ed,
If I understand you correctly it would be a lot better / easier
to eliminate the concept of having a Print Area
and instead remove the unused rows and columns from the
used range.
http://www.mvps.org/dmcritchie/excel/lastcell.htm#makelastcell

To let Excel find where your lastcell should be you can use
Debra's macro at
Why do my scrollbars go to row 500 -- my data ends in cell E50?,--
http://www.contextures.com/xlfaqApp.html#Unused

You can determine what the printarea is using a macro, I do that
on my buildtoc.htm page.
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

 

[Ed]

David - I looked on your BuildTOC page, but couldn't find where you
reference and use the PrintArea range. In fact, even though Excel help says
it returns a range, I can't set a reference to it to use it. But I do
notice that when setting up the PrintOut expression, there is a Pages
option, which referes to the actual page that would be printed. But I
wasn't able to get a reference to it, either.

If you have any further info, I would be grateful for the help.

Ed

 

[David McRitchie]

Hi Ed,
find on printarea had no problem finding table, 2nd table below
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sheets
or the following line of code:
Cells(cRow - 1 + cSht, cCol + 7) = Sheets(cSht).PageSetup.PrintArea

But the main point that I want to make is that the use of a
Print Area should normally only be used as a last resort,
you should be fixing your lastcell indication, so that you aren't
printing extra pages.

BTW, Print Preview prints everything, there is no selection, at
least not in Excel 2000, so the range would be the usedrange
that you can find with Ctrl+End
MsgBox ActiveSheet.UsedRange.Address(0, 0)

 

[Ed]

Thanks for sticking with me, David.

Hi Ed,
find on printarea had no problem finding table, 2nd table below
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sheets
or the following line of code:
Cells(cRow - 1 + cSht, cCol + 7) = Sheets(cSht).PageSetup.PrintArea

Yes, I saw that. But I can't see how that allows you access to the rnage of
cells in the PrintArea. I'm sorry if I just don't get it; I admit my
undestanding in this area is very limited. It looks to me like you are
setting the PrintArea. I'll look at it some more.

But the main point that I want to make is that the use of a
Print Area should normally only be used as a last resort,
you should be fixing your lastcell indication, so that you aren't
printing extra pages.

The big issue I face is when the text in a cell overflows into the next
print area. or there is one small comment line only in the next print area.
I don't set up these worksheets - I just get to work with them. My idea was
to see if I cold find the rows and columns on the limits of each PrintArea,
set a range bounded by those cells, and then check certain cells in that
range for content; if there is none, then that is not a useful section to
print out and I can quit wasting paper. This would be in a macro that would
cycle through each worksheet of a workbook.

If this is an impossible idea, then so be it. Sometimes it's just nice to
dream. Thanks for the boost.

Ed