How to detect when a named cell (range) is Blank in VBA?

[Richard]

I have named cells that are either single cells of ranges.
I need to detect, using VBA, is those named cells are empty or any
value, INCLUDING formulas that would (incidently) return "" !!

I've been scouring google for the answer to no avail

So to recap: How can I detect (a true/false is fine) if a named cell
(which can be a range/single cell) is Blank AND it does NOT contain any
formulas (it happends that some Named Cells 'arrays' have formulas that
return "" )

Many thanks for your help!

Richard

 

[Jim Cone]

Richard,

For an entire range you can use... Application.CountA(Range("MyNamedRange")
For a single cell...
The Len function is very fast... x = Len(ActiveCell)
(however, a single apostrophe ' in a cell is not counted)
x = IsEmpty(ActiveCell) seems to be the choice of many.

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Richard" <[email protected]>
wrote in message
I have named cells that are either single cells of ranges.
I need to detect, using VBA, is those named cells are empty or any
value, INCLUDING formulas that would (incidently) return "" !!
I've been scouring google for the answer to no avail
So to recap: How can I detect (a true/false is fine) if a named cell
(which can be a range/single cell) is Blank AND it does NOT contain any
formulas (it happends that some Named Cells 'arrays' have formulas that
return "" )
Many thanks for your help!
Richard

 

[Guest]

Hi Richard,

Have you tried SpecialCells? e.g. (this returns the addresses of blank cells)
Sub WotAboutThis()
MsgBox Cells.SpecialCells(xlCellTypeBlanks).Address(0, 0)
End Sub

 

[Richard]

ok that Application.CountA(Range("MyNamedRange") worked for all named
cells! (ranges or not)

Gosh that seems so simple...!

THANK YOU very much!
Richard