I run into this problem most frequently when I have a variable-length
report. If my data ends in row 35, for example, I don't want to print the
zeros and error values that formulas below row 35 might be returning.
If this is your problem, you might define a dynamic print range.
To do so, first set up a cell outside your print range, a cell that
calculates the number of rows of data that need to be printed. Let's name
that cell Sheet1!NumRows (assuming you're in Sheet1). Then enter a value,
say 35, in the NumRows cell.
Next, define PrintArea (without the underscore) as a dynamic range name. To
do so, choose Insert, Name, Define, enter Sheet1!PrintArea as the name
(again, assuming you're in Sheet1) and enter the following formula in the
Refers To box:
=OFFSET(Sheet1!$A$1,0,0,NumRows,10)
This formula defines PrintArea in Sheet1 to begin in cell A1. The area is
NumRows high and 10 columns wide. (Of course, you can use your own settings
for the top left cell and the number of columns.) You can test this name by
hitting the F5 key, and entering PrintArea as the reference to go to. (The
PrintArea name *won't* be displayed in the list box.) When you press Enter,
Excel will select the range A1:J30. If you enter 40 in the NumRows cell,
then go to PrintArea again, Excel will select the range A1:J40.
Finally, to turn this into a *real* print area, define Print_Area as
=Sheet1!PrintArea.
When I first started to use this trick in Excel 3 I began by defining
Print_Area with the OFFSET formula. But I found that Excel had the nasty
habit of redefining my print range definition under various circumstances,
blowing away my OFFSET formula. By storing the formula in the PrintArea
name, I can easily re-establish the dynamic print area whenever Excel
destroys it.
Charley Kyd
