Benifit of AREAS

L

LoveCandle

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,
 
B

Bob Phillips

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)
 
R

Ron Rosenfeld

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
Click to expand...

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

Bob Phillips

Thought I was in the programming group!

--

HTH

Bob Phillips

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

Ron Rosenfeld

Thought I was in the programming group!
Click to expand...

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


--ron
 
L

LoveCandle

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

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

JMB

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

Biff

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
 
J

JMB

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

Biff

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

Biff
 
L

Leo Heuser

"LoveCandle" <[email protected]> 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,
Click to expand...


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
 
H

Harlan Grove

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

How can we use it with other functions?
Click to expand...
....

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

Ron Rosenfeld

"LoveCandle" <[email protected]> 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
Click to expand...

Thanks for that example, Leo.
--ron
 
L

LoveCandle

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,
 
L

Leo Heuser

"LoveCandle" <[email protected]> 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,
Click to expand...

You're welcome and thanks for the feedback :)

Leo Heuser
 
Top