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

Hi Sige,

Try:

Application.CountA(Selection) = 0

---
Regards,
Norman



"Sige" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Thx Norman!

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

rather than encasulate it in a UDF

--
Regards,
Tom Ogilvy

"Sige" <[email protected]> wrote in message
news:[email protected]...
> Thx Norman!
>

Hi Norman,
That simple thx!

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

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

seems to work.

--
Regards,
Tom Ogilvy


"Sige" <[email protected]> wrote in message
news:[email protected]...
> 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 ...
>

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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Hi Dana,

Was exactly where I was looking for!
Thx 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

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" <[email protected]> wrote in message
news:[email protected]...
> Hi Dana,
>
> Was exactly where I was looking for!
> Thx Sige
>

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

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?

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

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

Hi Dave,

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

Cheers Sige

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

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