Make the #N/A sign invisible

R

rixanna

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
N

Nick Hodge

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

[email protected]
www.nickhodge.co.uk
 
Top