Stopping blank cells with formulae in from printing
 

I have created a new workbook and in cell B1 I have put the formulae
"=IF(ISBLANK(A1),"",44)". I have then used the fill down function to fill it
down to row 65536. When I go to file print preview its going to print 1821
blank pages. How can I stop it from printing cells that show no value but
contain formulae?

Stopping blank cells with formulae in from printing
 

Have you tried using the autofilter option? It is in the Filter submenu on
the Data menu. You can set it so items with a value of zero are not
displayed.

"nickname" wrote:

I have created a new workbook and in cell B1 I have put the formulae
"=IF(ISBLANK(A1),"",44)". I have then used the fill down function to fill it
down to row 65536. When I go to file print preview its going to print 1821
blank pages. How can I stop it from printing cells that show no value but
contain formulae?

Stopping blank cells with formulae in from printing
 

No that does not do what i want it to :-(

Thanks anyway

Anybody else have any suggestions?

"AndyRoo" wrote:

Have you tried using the autofilter option? It is in the Filter submenu on
the Data menu. You can set it so items with a value of zero are not
displayed.

"nickname" wrote:

I have created a new workbook and in cell B1 I have put the formulae
"=IF(ISBLANK(A1),"",44)". I have then used the fill down function to fill it
down to row 65536. When I go to file print preview its going to print 1821
blank pages. How can I stop it from printing cells that show no value but
contain formulae?

Stopping blank cells with formulae in from printing
 

nickname,
Works for me.

If that not good enough, look into manipulating the .PrintArea.

NickHK

"nickname" wrote in message
...
No that does not do what i want it to :-(

Thanks anyway

Anybody else have any suggestions?

"AndyRoo" wrote:

Have you tried using the autofilter option? It is in the Filter submenu
on
the Data menu. You can set it so items with a value of zero are not
displayed.

"nickname" wrote:

I have created a new workbook and in cell B1 I have put the formulae
"=IF(ISBLANK(A1),"",44)". I have then used the fill down function to
fill it
down to row 65536. When I go to file print preview it's going to print
1821
blank pages. How can I stop it from printing cells that show no value
but
contain formulae?

Stopping blank cells with formulae in from printing
 

I'd use data|filter and filter that column to show the nonblanks (not the zeros
that Andy suggested).


How about an alternative?

I'm gonna use column B as a column that always has something in it if that row
should print. You can change that if your formula is in a different column.

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$B$1:$B$2000<""),ROW(Sheet1!$B$1:$B$2000) )

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column. In fact, I'd bet you don't need to go to row 65536!)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5)

That last 5 represents the last column to print (A:E). Change it to what you
want.

And change the worksheet (sheet1) if necessary (in all the places).



nickname wrote:

I have created a new workbook and in cell B1 I have put the formulae
"=IF(ISBLANK(A1),"",44)". I have then used the fill down function to fill it
down to row 65536. When I go to file print preview its going to print 1821
blank pages. How can I stop it from printing cells that show no value but
contain formulae?

--

Dave Peterson

Stopping blank cells with formulae in from printing
 

Hi,

Based on my understanding, now the question is: How to stop Excel from
printing cells that show no value but contain formulae? Please correct me
if I have any misunderstandings.

I agree with NickHK here. Print_Area is a defined name which can be
defined using a formula - same as setting up a dynamic range for a chart.

For an example:
Name: Sheet1!Print_Area
Refers to:
=offset(Sheet1!$A$1,0,0,Countif(Sheet1!$A:$A,"<") ,10)

It would define an area that is 10 columns wide and as many rows contain
non blank values. This count depends on a column that will have entries
for all rows from the first to the last one you want printed. If you will
have spaces, then you would use an array formula to get the max row with a
value in it.

Thanks very much and have a good day.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
Register to Access MSDN Managed Newsgroups!
http://msdn.microsoft.com/subscripti...gednewsgroups/

This posting is provided "AS IS" with no warranties, and confers no rights.

Stopping blank cells with formulae in from printing
 

Hi Nickname,

Did TungCheWah and Yan-Hong Huang's suggestion helps you?
If you still have any concern, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.