Hi,

What is the benifit of the worksheet function AREAS?

How can we use it with other functions?

Can you please give me an example for that?

Thank you everybody,


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=566444

A very simple example

Dim rng As Range
Dim area As Range

Set rng = Range("A1:A2,B9:C15,M4:O11")
For Each area In rng.Areas
MsgBox area.Address
Next area


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"LoveCandle" wrote
in message ...

Hi,

What is the benifit of the worksheet function AREAS?

How can we use it with other functions?

Can you please give me an example for that?

Thank you everybody,


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile:
http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=566444

On Sun, 30 Jul 2006 20:59:51 +0100, "Bob Phillips"
wrote:

A very simple example

Dim rng As Range
Dim area As Range

Set rng = Range("A1:A2,B9:C15,M4:O11")
For Each area In rng.Areas
MsgBox area.Address
Next area

What about the Areas "Worksheet Function"?
--ron

Thought I was in the programming group!

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Ron Rosenfeld" wrote in message
...
On Sun, 30 Jul 2006 20:59:51 +0100, "Bob Phillips"
wrote:

A very simple example

Dim rng As Range
Dim area As Range

Set rng = Range("A1:A2,B9:C15,M4:O11")
For Each area In rng.Areas
MsgBox area.Address
Next area

What about the Areas "Worksheet Function"?
--ron

On Mon, 31 Jul 2006 00:08:09 +0100, "Bob Phillips" wrote:

Thought I was in the programming group!

Yeah. I've never used the Areas worksheet function and was also wondering about
how it might be useful.


--ron

Thank you for this useful example for AREAS worksheet function in VBA
,,

But, I am greedy to have another example for the same function on the
worksheet itself,,


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=566444

I had to look it up in help to make sure there was such a worksheet function.
I've never seen anybody use it, FWVLIW.


"Ron Rosenfeld" wrote:

On Mon, 31 Jul 2006 00:08:09 +0100, "Bob Phillips" wrote:

Thought I was in the programming group!

Yeah. I've never used the Areas worksheet function and was also wondering about
how it might be useful.


--ron

Well, it was either the AREAS(Range) function or the CONCAT(Range, ",")
function. There wasn't room for both!

There was so much demand for the Areas function that it got the nod.

Biff

"JMB" wrote in message
...
I had to look it up in help to make sure there was such a worksheet
function.
I've never seen anybody use it, FWVLIW.


"Ron Rosenfeld" wrote:

On Mon, 31 Jul 2006 00:08:09 +0100, "Bob Phillips"
wrote:

Thought I was in the programming group!

Yeah. I've never used the Areas worksheet function and was also wondering
about
how it might be useful.


--ron

They need to do more market research. Or maybe they're saving it for Office
2010? I'm sure it's on the to do list (right after fixing COMBIN).

It would be nice to have a CONCAT that would work w/ranges and in array
formulae. I've wanted to do that more than once.


"Biff" wrote:

Well, it was either the AREAS(Range) function or the CONCAT(Range, ",")
function. There wasn't room for both!

There was so much demand for the Areas function that it got the nod.

Biff

"JMB" wrote in message
...
I had to look it up in help to make sure there was such a worksheet
function.
I've never seen anybody use it, FWVLIW.


"Ron Rosenfeld" wrote:

On Mon, 31 Jul 2006 00:08:09 +0100, "Bob Phillips"
wrote:

Thought I was in the programming group!

Yeah. I've never used the Areas worksheet function and was also wondering
about
how it might be useful.


--ron

I've never used AREAS, never seen it used, and like Ron, wonder how it can
be used for anything useful!

Biff

"JMB" wrote in message
...
They need to do more market research. Or maybe they're saving it for
Office
2010? I'm sure it's on the to do list (right after fixing COMBIN).

It would be nice to have a CONCAT that would work w/ranges and in array
formulae. I've wanted to do that more than once.


"Biff" wrote:

Well, it was either the AREAS(Range) function or the CONCAT(Range, ",")
function. There wasn't room for both!

There was so much demand for the Areas function that it got the nod.

Biff

"JMB" wrote in message
...
I had to look it up in help to make sure there was such a worksheet
function.
I've never seen anybody use it, FWVLIW.


"Ron Rosenfeld" wrote:

On Mon, 31 Jul 2006 00:08:09 +0100, "Bob Phillips"
wrote:

Thought I was in the programming group!

Yeah. I've never used the Areas worksheet function and was also
wondering
about
how it might be useful.


--ron

"LoveCandle" skrev
i en meddelelse
...

Hi,

What is the benifit of the worksheet function AREAS?

How can we use it with other functions?

Can you please give me an example for that?

Thank you everybody,


--
LoveCandle


Hi

Here's an example of using AREAS

Assume you have a named range (TestBlock) of non
contiguous blocks e.g. A1:A10 + C1:C10 + E1:E10 +
G1:G10 and that this range is expanded every third day.
The name is adjusted to hold the new block as well.


The formula

=INDEX(TestBlock,3,1,AREAS(TestBlock)-1)

will always return the value of the third cell in the last
block but one


--
Best regards
Leo Heuser

Followup to newsgroup only please.

LoveCandle wrote...
What is the benifit of the worksheet function AREAS?

How can we use it with other functions?
....

Homework?

AREAS is one of the less useful information functions. It's only use is
returning the number of areas in a range reference, and there's little
or no need for that since it's unnecessary in functions that can handle
general range references (like SUM), and it's unnecessary to deal with
functions that can handle only single area ranges (like SUMIF) since
one may use INDEX(Range,0,0,1) to refer to the first area of a general
range reference.

It ranks right up there with INFO, both of which are more useful than
POWER or CONCATENATE.

On Mon, 31 Jul 2006 10:05:09 +0200, "Leo Heuser"
wrote:

"LoveCandle" skrev
i en meddelelse
news:LoveCandle.2brt35_1154283308.5862@excelfor um-nospam.com...

Hi,

What is the benifit of the worksheet function AREAS?

How can we use it with other functions?

Can you please give me an example for that?

Thank you everybody,


--
LoveCandle


Hi

Here's an example of using AREAS

Assume you have a named range (TestBlock) of non
contiguous blocks e.g. A1:A10 + C1:C10 + E1:E10 +
G1:G10 and that this range is expanded every third day.
The name is adjusted to hold the new block as well.


The formula

=INDEX(TestBlock,3,1,AREAS(TestBlock)-1)

will always return the value of the third cell in the last
block but one

Thanks for that example, Leo.
--ron

"Ron Rosenfeld" skrev i en meddelelse
...
Hi

Here's an example of using AREAS

Assume you have a named range (TestBlock) of non
contiguous blocks e.g. A1:A10 + C1:C10 + E1:E10 +
G1:G10 and that this range is expanded every third day.
The name is adjusted to hold the new block as well.


The formula

=INDEX(TestBlock,3,1,AREAS(TestBlock)-1)

will always return the value of the third cell in the last
block but one

Thanks for that example, Leo.
--ron

My pleasure, Ron :-)
Leo

Thank you Mr. Leo for the example you mentioned ..

and thanks for everyboy participated in this topic

I thought that excel worksheet functions can't get a range consists of
different areas ,, but with help of AREAS worksheet function we could
do that.

Thank you again,


--
LoveCandle
------------------------------------------------------------------------
LoveCandle's Profile: http://www.excelforum.com/member.php...o&userid=28612
View this thread: http://www.excelforum.com/showthread...hreadid=566444

"LoveCandle" skrev
i en meddelelse
...

Thank you Mr. Leo for the example you mentioned ..

and thanks for everyboy participated in this topic

I thought that excel worksheet functions can't get a range consists of
different areas ,, but with help of AREAS worksheet function we could
do that.

Thank you again,


You're welcome and thanks for the feedback :-)

Leo Heuser