Tags: cell, cells, detect, empty, excel, including, microsoft, msdn, named, range, ranges, single, software, value, vba

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

On Microsoft » Microsoft Excel

2,759 words with 3 Comments; publish: Fri, 23 May 2008 03:45:00 GMT; (30678.13, « »)

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

All Comments

Leave a comment...

  • 3 Comments
    • 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" <richard.taillon.excel.questionfor.info.gmail.com>

      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

      #1; Fri, 23 May 2008 03:46:00 GMT
    • 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

      --

      The major part of getting the right answer lies in asking the right

      question...

      "Richard" wrote:

      > 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

      >

      #2; Fri, 23 May 2008 03:47:00 GMT
    • ok that Application.CountA(Range("MyNamedRange") worked for all named

      cells! (ranges or not)

      Gosh that seems so simple...!

      THANK YOU very much!

      Richard

      #3; Fri, 23 May 2008 03:48:00 GMT