Make the #N/A sign invisible

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rixanna - 17 Oct 2006 02:03 GMT

I've developed a simple program using Microsoft Excel and Visual Basic
Editor. In the worksheet, I have specified 10 cells according to their
own formula. All the cells contain #N/A sign as to show that the cells
contain their own formula ( I guess!)

After executing the program only 6 cells were automatically fill-in
with data. What can I do if I want the program to consider the other 4
cells as empty? (eventhough they actually have formulas)

This is because I want the program to automatically hide the empty
cells.(cells with no data)

Anyone can help me?
Thank you in advanced

Reply to this Message

Ron Rosenfeld - 17 Oct 2006 02:17 GMT

>I've developed a simple program using Microsoft Excel and Visual Basic
>Editor. In the worksheet, I have specified 10 cells according to their
[quoted text clipped - 10 lines]
>Anyone can help me?
>Thank you in advanced

You could use conditional formatting:

Select the cells
Format/Condtional Formatting
Formula Is: =ISNA(cell_ref)
Format
Font
Color -- select same color as the cell background (e.g. white)

--ron

Reply to this Message

Nick Hodge - 17 Oct 2006 07:24 GMT

Rixanna

The standard convention would be to wrap the formula to trap the error. If
it is always #N/A then you can use ISNA. e.g

=IF(ISNA('YourFormula'),"",'YourFormula')

This will return a 'blank' cell if the result of your formula is #N/A

The other functions in this error 'group' are

ISERR - handles all errors BAR #N/A
ISNA - handles #N/A errors
ISERROR - handles ALL errors

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
www.nickhodge.co.uk

> I've developed a simple program using Microsoft Excel and Visual Basic
> Editor. In the worksheet, I have specified 10 cells according to their
[quoted text clipped - 10 lines]
> Anyone can help me?
> Thank you in advanced

Reply to this Message