ISBLANK for a Range
 

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

ISBLANK for a Range
 

Hi Sige,

Try:

Application.CountA(Selection) = 0

---
Regards,
Norman



"Sige" wrote in message
oups.com...
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

ISBLANK for a Range
 

Hi Sige,

As you asked for a UDF, try:

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

---
Regards,
Norman



"Sige" wrote in message
oups.com...
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

ISBLANK for a Range
 

Thx Norman!

ISBLANK for a Range
 

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

rather than encasulate it in a UDF

--
Regards,
Tom Ogilvy

"Sige" wrote in message
oups.com...
Thx Norman!

ISBLANK for a Range
 

Hi Norman,
That simple thx!

ISBLANK for a Range
 

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 ...

ISBLANK for a Range
 

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

seems to work.

--
Regards,
Tom Ogilvy


"Sige" wrote in message
oups.com...
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 ...

ISBLANK for a Range
 

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

ISBLANK for a Range
 

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 :)
--
Dana DeLouis
Win XP & Office 2003


"Sige" wrote in message
ups.com...
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

ISBLANK for a Range
 

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",";;;")

Dana DeLouis wrote:

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 :)
--
Dana DeLouis
Win XP & Office 2003

"Sige" wrote in message
ups.com...
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


--

Dave Peterson

ISBLANK for a Range
 

Hi Dana,

Was exactly where I was looking for!
Thx Sige

ISBLANK for a Range
 

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

ISBLANK for a Range
 

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.

--
Regards,
Tom Ogilvy

"Sige" wrote in message
oups.com...
Hi Dana,

Was exactly where I was looking for!
Thx Sige

ISBLANK for a Range
 

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

If the formula returns text, you could use:

Sige wrote:

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

--

Dave Peterson

ISBLANK for a Range
 

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?

ISBLANK for a Range
 

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

ISBLANK for a Range
 

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 wrote:

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?

--

Dave Peterson

ISBLANK for a Range
 

Hi Dave,

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

Cheers Sige

ISBLANK for a Range
 

I think using the newsgroup is better.

Sige wrote:

Hi Dave,

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

Cheers Sige

--

Dave Peterson

ISBLANK for a Range
 

I ll give it a try again ...

Dave Peterson wrote:
I think using the newsgroup is better.

Sige wrote:

Hi Dave,

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

Cheers Sige

--

Dave Peterson