prevent empty rows from printing

I have formulae in cells to reference another sheet, but if the referenced
cell is blank, then the new cell I made blank (using the IF function). The
problem is, when I want to print, Excel wants to print all of the cells with
entries in them - even the ones with formulae in them that are blank.

Is there any way to prevent the empty cells from printing?

Clarification:
I have copied the formulae down several hundred rows, because it is a
template that will receive different sized files. So there are several
hundred rows with formulae in them, but they show up blank, and I would like
to avoid printing the blank cells.

If the rows are either empty or contain formulae returning "empty", then hide
them
--
Gary's Student


"dill_weed" wrote:

> Clarification:
> I have copied the formulae down several hundred rows, because it is a
> template that will receive different sized files. So there are several
> hundred rows with formulae in them, but they show up blank, and I would like
> to avoid printing the blank cells.

dill_weed wrote...
>I have copied the formulae down several hundred rows, because it is a
>template that will receive different sized files. So there are several
>hundred rows with formulae in them, but they show up blank, and I would like
>to avoid printing the blank cells.

If all these blank rows will be clustered below the nonblank rows, you
could define the print area for the worksheet using a formula. For
example, if the worksheet were named WS, then run the menu command
Insert > Name > Define, enter WS!Print_Area in the topmost field in the
Define Name dialog and enter the formula

=OFFSET(WS!$A$1:$J$1,0,0,
LOOKUP(2,1/(WS!$A$2:$A$65536<>""),ROW(WS!$A$2:$A$65536)))

in the 'Refers to' entry field, then click OK. Replace the worksheet
name, WS, as needed. Also note that the print range could start in any
cell, so replace the first argument to OFFSET, WS!$A$1:$J$10 in the
formula above, with the range address of the topmost row of your
intended print range, and adjust the LOOKUP call as needed to
correspond to changes in the location of the print range (note that
both ranges in the LOOKUP call begin in the cell immediately below the
top-left cell of the print range).

What's wrong with using File->Print Area->Set Print Area

"dill_weed" wrote:

> Clarification:
> I have copied the formulae down several hundred rows, because it is a
> template that will receive different sized files. So there are several
> hundred rows with formulae in them, but they show up blank, and I would like
> to avoid printing the blank cells.

Sloth wrote...
>What's wrong with using File->Print Area->Set Print Area

Nothing if you're the sort of person who likes to do everything
manually, but some people prefer that tasks that could be automated are
automated.

exactly - thank you harland. I am creating a template for others to use, and
I want entries to load automatically (referenced into several sheets), and
then be able to print easily - without making the user set a new print area
every for every single file that they create. As it is now, they will either
have to set a new print area every time they want to print a file, or if they
don't they will get several sheets of blank cells (because there are formulas
in them).

"Harlan Grove" wrote:

> Sloth wrote...
> >What's wrong with using File->Print Area->Set Print Area
>
> Nothing if you're the sort of person who likes to do everything
> manually, but some people prefer that tasks that could be automated are
> automated.
>
>

Wow, settle down guys. I meant no offence, and was just offering a
suggestion. I didn't know if you even considered that an option.

"dill_weed" wrote:

> exactly - thank you harland. I am creating a template for others to use, and
> I want entries to load automatically (referenced into several sheets), and
> then be able to print easily - without making the user set a new print area
> every for every single file that they create. As it is now, they will either
> have to set a new print area every time they want to print a file, or if they
> don't they will get several sheets of blank cells (because there are formulas
> in them).
>
> "Harlan Grove" wrote:
>
> > Sloth wrote...
> > >What's wrong with using File->Print Area->Set Print Area
> >
> > Nothing if you're the sort of person who likes to do everything
> > manually, but some people prefer that tasks that could be automated are
> > automated.
> >
> >

Sloth wrote...
>Wow, settle down guys. I meant no offence, and was just offering a
>suggestion. I didn't know if you even considered that an option.
....

No, no one would have considered the obvious before you mentioned it.
You have everyone's deepest gratitude for mentioning it.

I just read a post where I guy wanted a function to replace a certain string
of characters with another one. Then he responded saying he used the
"replace" function. "Duh" he said. He knew the obvious, just not at that
moment. It's possible the OP could have had a similar brain fart, and just
needed a reminder.

It sounded too me like a perfect scenario for Set Print Area. Usually when
people want options other than the obvious they say "I don't want to Set
Print Area" or "I don't want to Sort" or "I don't want to use the ROUND
function" etc.

Also, the OP could have been really new to Excel and not even known about
Set Print Area. I used excel all through High School and College without
ever knowing you could set the print area.

Sometimes people like doing things the easiest, or most direct way. There
are a million reasons why my post was appropriate.

My post might not have been helpful to the OP, but it didn't hurt anything.
Your posts however are harmful (not as in my feelings are hurt). This forum
is good because of the community. You start posting stuff like that, and
this forum turns into a trolling forum. People will be afraid to post
anything, questions or answers. I honestly would like 7 wrong answers to one
of my questions, than no answers at all. People can still benefit from wrong
answers and obvious answers.

By the way your solution was very good. It works perfectly. I couldn't
find anything in the help though. The only thing I could find was naming
cells or ranges of cells. Do you have any suggestions where to look in the
help, or any links on the subject?

"Harlan Grove" wrote:

> Sloth wrote...
> >Wow, settle down guys. I meant no offence, and was just offering a
> >suggestion. I didn't know if you even considered that an option.
> ....
>
> No, no one would have considered the obvious before you mentioned it.
> You have everyone's deepest gratitude for mentioning it.
>
>

Sloth wrote...
>I just read a post where I guy wanted a function to replace a certain string
>of characters with another one. Then he responded saying he used the
>"replace" function. "Duh" he said. He knew the obvious, just not at that
>moment. It's possible the OP could have had a similar brain fart, and just
>needed a reminder.
....

Possible. We differ on whether to start off assuming OPs are imbeciles
or newbies. Generally best to begin by assuming they're not, then
change to simpler approaches when their follow-ups make it clear they
need simple solutions.

>It sounded too me like a perfect scenario for Set Print Area. Usually when
>people want options other than the obvious they say "I don't want to Set
>Print Area" or "I don't want to Sort" or "I don't want to use the ROUND
>function" etc.

It was a perfect scenario for Print_Area (the defined name), but it can
be set using a function that can modify the print range dynamically
based on how cells evaluate.

>Also, the OP could have been really new to Excel and not even known about
>Set Print Area. I used excel all through High School and College without
>ever knowing you could set the print area.
....

The OP was unlikely to have been a newbie since it was clear the OP
knew how to refer to other worksheets and use the IF function to mask
unwanted results.

>My post might not have been helpful to the OP, but it didn't hurt anything.
>Your posts however are harmful (not as in my feelings are hurt). This forum
>is good because of the community. You start posting stuff like that, and
>this forum turns into a trolling forum. People will be afraid to post
>anything, questions or answers. I honestly would like 7 wrong answers to one
>of my questions, than no answers at all. People can still benefit from wrong
>answers and obvious answers.

Hard to see how my responses will deter OPs. Similar responses to other
respondents in the past don't seem to have reduced newsgroup traffic.

>By the way your solution was very good. It works perfectly. I couldn't
>find anything in the help though. The only thing I could find was naming
>cells or ranges of cells. Do you have any suggestions where to look in the
>help, or any links on the subject?

Read the newsgroups. They're more helpful than online help. Also,
experiment.

Originally Posted by Harlan Grove

dill_weed wrote...
>I have copied the formulae down several hundred rows, because it is a
>template that will receive different sized files. So there are several
>hundred rows with formulae in them, but they show up blank, and I would like
>to avoid printing the blank cells.

If all these blank rows will be clustered below the nonblank rows, you
could define the print area for the worksheet using a formula. For
example, if the worksheet were named WS, then run the menu command
Insert > Name > Define, enter WS!Print_Area in the topmost field in the
Define Name dialog and enter the formula

=OFFSET(WS!$A$1:$J$1,0,0,
LOOKUP(2,1/(WS!$A$2:$A$65536<>""),ROW(WS!$A$2:$A$65536)))

in the 'Refers to' entry field, then click OK. Replace the worksheet
name, WS, as needed. Also note that the print range could start in any
cell, so replace the first argument to OFFSET, WS!$A$1:$J$10 in the
formula above, with the range address of the topmost row of your
intended print range, and adjust the LOOKUP call as needed to
correspond to changes in the location of the print range (note that
both ranges in the LOOKUP call begin in the cell immediately below the
top-left cell of the print range).

I must say this is truly wonderful. I've been looking for a way to dynamically set the print area that is ideal for a long time without messing with hiding rows and cells and such. This has been working perfectly on all of my templates so far!

Thanks!

Originally Posted by Harlan Grove

dill_weed wrote...
>I have copied the formulae down several hundred rows, because it is a
>template that will receive different sized files. So there are several
>hundred rows with formulae in them, but they show up blank, and I would like
>to avoid printing the blank cells.

If all these blank rows will be clustered below the nonblank rows, you
could define the print area for the worksheet using a formula. For
example, if the worksheet were named WS, then run the menu command
Insert > Name > Define, enter WS!Print_Area in the topmost field in the
Define Name dialog and enter the formula

=OFFSET(WS!$A$1:$J$1,0,0,
LOOKUP(2,1/(WS!$A$2:$A$65536<>""),ROW(WS!$A$2:$A$65536)))

in the 'Refers to' entry field, then click OK. Replace the worksheet
name, WS, as needed. Also note that the print range could start in any
cell, so replace the first argument to OFFSET, WS!$A$1:$J$10 in the
formula above, with the range address of the topmost row of your
intended print range, and adjust the LOOKUP call as needed to
correspond to changes in the location of the print range (note that
both ranges in the LOOKUP call begin in the cell immediately below the
top-left cell of the print range).

This is an old thread, but it is still useful. I agree with Vassago that this is awesome!

I had to spend a few minutes studying it to understand how it works.
For those interested:
Syntax: OFFSET(reference, rows, cols, [height], [width])
where rows and cols are the number of rows and columns to offset from the area "reference", and height and width are the number of rows and columns to include in the resulting area.
If height and width are omitted, the OFFSET function defaults to the height and width of the reference area.
Syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])
If the LOOKUP function doesn't find the lookup value (in this case 2), it returns the row of the largest value it finds.
The fancy bit that I didn't know was that LOOKUP can do array formulas, not just arrays.
So, 1/(cell<>"") returns 1 if the cell has a value in it and an error [#Div/0!] if not.
The resulting lookup_vector is a 1 in every row with a value, and an error in the rest.
The result_vector is the row number of each cell.
So, the whole LOOKUP formula looks in Column A and returns the row number of the last cell that is not empty.
NOTE: This is not the same as the number of rows which have values in them, nor is it the number of rows between the reference and the last row with a value.
The OFFSET function then returns the area that starts with reference, and expands it down so that it has the number of rows equal to the row number of the last cell in column A with a value.

This was perfect for dill_weed's original situation. I had a different situation.
If you don't want to print the first 5 rows, and you change the reference to, say row 6. It will then print 5 empty rows at the bottom.
If you want it to start at row 6 and print only to the end of the values you can either
1) leave the reference at row 1 and put 5 in as the "rows" argument in the OFFSET function or
2) Put the reference at WS!$A$6:$A$65536

Either way, you need to subtract 5 from height (the LOOKUP function answer)

=OFFSET(WS!$A$1:$J$1,5,0,LOOKUP(2,1/(WS!$A$2:$A$65536<>""),ROW(WS!$A$2:$A$65536))-5)

I also had the issue where I didn't have values in column A in every row. I needed to check columns C and F.

I modified Harlan Grove's excellent technique to set the print area to the last row with any cell in column C or F not empty:

=OFFSET(WS!$A$1:$J$1,0,0,MAX(LOOKUP(2,1/(WS!$C$2:$C$65536<>""),ROW(WS!$A$2:$A$65536)),LOOKUP(2,1/(WS!$F$2:$F$65536<>""),ROW(WS!$A$2:$A$65536))))

But then I realized that if the name manager and LOOKOUT functions can handle array functions I could use a MAX(IF( to generalize it.

This sets the Print_Area to all columns in the reference area down to the last value in any of the columns:

=OFFSET(WS!$A$1:$J$1,0,0,MAX(IF(WS!$A$2:$J$65536<>"",ROW(WS!$A$2:$A$65536),0)))

Cheers!
Dudley Sirius