Hide #NUM from a sheet using excel VBA

Posted by Raj Nair on October 10, 2001 6:41 AM

I wish to hide the #NUM from a worksheet - but not remove the formula.

If I wish to look through every cell in a workbook, hide the #NUM with white text so that it is invisible, how do I manage it ?

Thanks in advance.

I have tried the =ISERROR() function, but I need row references like A2, A3 etc. I am unaware of any ways to get row/col references like that from within VBA.

Posted by Dank on October 10, 2001 6:48 AM

Hello,

Although it might be neater to avoid the error in the first place this macro will do what you need.

Sub MakeErrorsWhite()
Dim sht As Worksheet

Set sht = ActiveSheet

'First make everything black (you could leave this out)
sht.UsedRange.Font.Color = RGB(0, 0, 0)

'Now make just the errors white
sht.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).Font.Color = RGB(255, 255, 255)

End Sub

Hope it helps,
Daniel.

Posted by raj nair on October 10, 2001 7:54 AM