ISBLANK for a Range

[Sige]

Hi There,

ISBLANK-function works only for a a single cell -to my knowledge-.
Anyone has a UDF which can evaluate a range?

Brgds
Sige

 

[Norman Jones]

Hi Sige,

Try:

Application.CountA(Selection) = 0

 

[Norman Jones]

Hi Sige,

As you asked for a UDF, try:

Function RangeBlank(Rng As Range) As Boolean
RangeBlank = Application.CountA(Rng) = 0
End Function

 

[Sige]

Thx Norman!

 

[Tom Ogilvy]

why not just =If(counta(rng)=0,"Blank","Not Blank")

rather than encasulate it in a UDF

 

[Sige]

Hi Norman,
That simple thx!

 

[Sige]

Indeed Tom, why not ;o)

PS:
I remember there was a way to add a "sort-of-comment" in a formula...
without affecting the formula.

Something like eg.:
=If(counta(rng)=0,"Blank","Not Blank") *T("This is function returns
true or false when range is empty")
or
=If(counta(rng)=0,"Blank","Not Blank") +Text("This is function returns
true or false when range is empty")
or
.... I have no idea anymore how it was ...

 

[Tom Ogilvy]

=If(counta(rng)=0,"Blank","Not Blank") &Left("This is function returns . . .
",0)

seems to work.

 

[Sige]

Hi Tom,

Thx ...it is actually not a concatentate that I am looking for! ...

I mean a function that does not produce an output in the cell (like
multiplying with 1, not to affect the formula)... but that you can use
to comment the formula in the cell itself...and which is only visible
when you select the cell.

Sige

 

[Dana DeLouis]

Hi. If a function returns a number (not a string), you can add zero without
affecting the answer. Was this what you were thinking of:
=MAX(A1:A4)+N("Your comment here")

HTH :>)

 

[Dave Peterson]

And just to add to Dana's suggestion...

If the formula returns text, you could use:

="This is a date " & text(a1,"mm/dd/yyyy") & text("your comment here",";;;")

 

[Sige]

Hi Dana,

Was exactly where I was looking for!
Thx Sige

 

[Sige]

Hi Dave,

& text("your comment here",";;;")

Was not what I was thinking about, but does the same imo ...
Besides that it converts the cell to Text.

Thx Sige

 

[Tom Ogilvy]

Just for interest, using your example and Dana's solution:

=IF(COUNTA(A1:A7)=0,"Blank","Not Blank")+N("Your comment here")

returns #Value. perhaps if you used an example that represented what you
were trying to achieve, it would have been easier to provide an answer to
suit.

 

[Dave Peterson]

Yep. That's why I included this line...

If the formula returns text, you could use:

 

[Sige]

Even when the formula returns a number no?

=SUM(A1:A5)& TEXT("your comment here";";;;")
=125&TEXT("your comment here";";;;")

MVG Sige



PS: Dave (alias Bob ;o) ), I would like to re-post my Outlining-problem
as I do not get out of the woods with it.
Any ideas how to formulate it ...to get your code working for
plural-level outlining, working?

 

[Sige]

Hi Tom,

It was something which I picked up a while ago .. but could not
remember what formula it was.
I thought it could be useful to explain myself - others in the
formula what the formula does.

& Learned now that the "Commenting" is not flawless... Thank You!

It was just a general question.

=IF(COUNTA(A1:A7)=0;"Blank";"Not Blank")+N("When cells are empty, this
formula returns an error ;o)")

Brgds Sige

 

[Dave Peterson]

Nope.

If you want a number, use N(). If you want text, use text(...,";;;").

I still don't understand how your outlining would work if you were doing it
manually.

 

[Sige]

Hi Dave,

Could I send you an email ..with an example workbook in it?

Cheers Sige

 

[Dave Peterson]

I think using the newsgroup is better.

Hi Dave,

Could I send you an email ..with an example workbook in it?

Cheers Sige