You the man! Thanks for your time and interest.

EM

"Tim Zych" wrote:

That goes back to the earlier question. Loop through and union rng, then
loop through the areas of rngUnion.

Sub CountAreas()
Dim rng As Range
Dim rngArea As Range
Dim rngUnion As Range
Set rng = Range("Sheet1!A1,Sheet1!A2,Sheet1!A3,Sheet1!A4")
Set rngUnion = rng.Areas(1)
For Each rngArea In rng.Areas
Set rngUnion = Union(rngUnion, rngArea)
Next
For Each rngArea In rngUnion.Areas
Debug.Print rngArea.Address(0, 0, , True)
Debug.Print rngArea.Parent.Name & "!" & rngArea.Address(0, 0)
Next
End Sub

--
Tim Zych
SF, CA

"ExcelMonkey" wrote in message
...
Sorry Tim. Maybe I have missed something. When I make the change, I get
the
full address with Sheet name for each individual cell range but I do not
get
contiguous cells expressed as a range.
Sub CountAreas()
Dim rng As Range
Dim rngArea As Range
Dim rngUnion As Range
Set rng = Range("Sheet1!A1,Sheet1!A2,Sheet1!A3,Sheet1!A4")
Set rngUnion = rng
For Each rngArea In rngUnion.Areas
Debug.Print rngArea.Address(0, 0, , True)
Debug.Print rngArea.Parent.Name & "!" & rngArea.Address(0, 0)
Next
End Sub

Immediate Window:
'[Address Range Builder.xls]Sheet1'!A1
Sheet1!A1
'[Address Range Builder.xls]Sheet1'!A2
Sheet1!A2
'[Address Range Builder.xls]Sheet1'!A3
Sheet1!A3
'[Address Range Builder.xls]Sheet1'!A4
Sheet1!A4

Thanks

EM



"Tim Zych" wrote:

Change this:

Set rngUnion = rng.Areas(1)

to

Set rngUnion = rng

rng.Areas(1) is the first area in rng.

--
Tim Zych
SF, CA
"ExcelMonkey" wrote in message
...
Thanks for the detail. Ignoring for the time-being the nuances, why is
it
that the union is not broken apart below? I only get the range C3
printed
to
the immediate window?

Sub CountAreas()
Dim rng As Range
Dim rngArea As Range
Dim rngUnion As Range
Set rng = Range("Sheet1!C3,Sheet1!D3,Sheet1!F1,Sheet1!G1")
Set rngUnion = rng.Areas(1)
For Each rngArea In rngUnion.Areas
Debug.Print rngArea.Address(0, 0, , True)
Debug.Print rngArea.Parent.Name & "!" & rngArea.Address(0, 0)
Next
End Sub

Immediate Window:
'[Address Range Builder.xls]Sheet1'!C3
Sheet1!C3


Regards

EM

"Tim Zych" wrote:

that instead of printing results as:

Sheet1!C3:D3,F1:G1

It would print as follows:

Sheet1!C3:D3,
Sheet1!F1:G1

Deconstruct the union:

For Each rngArea In rngUnion.Areas
Debug.Print rngArea.Address(0, 0, , True)
Debug.Print rngArea.Parent.Name & "!" & rngArea.Address(0, 0)
Next

Address has an External argument, giving a fully extended range's
address.
Not sure if that will help you because it also include the workbook
name.
Your way is valid too. One thing though (getting picky now, and this
is
extra info not necessarily relevant to the macro in use): if the sheet
has
spaces or single quotes in it, there will be a difference between the
printed lines above. Say instead of Sheet1, the sheet was names
"John's
Sheet", the macro will print the following:

Debug.Print rngArea.Address(0, 0, , True)
'[Book1]John''s Sheet'!C3:D3

Debug.Print rngArea.Parent.Name & "!" & rngArea.Address(0, 0)
John's Sheet!C3:D3

See the difference? The first one has opening and closing single
quotes,
and
the single quote in "John's" has been doubled.

Since you are setting the range object in the macro in the first
place,
it
assumes there has been a similar adjustment, wrapping single quotes
around
the sheet names, and doubling up the single quotes within the sheet
names,
e.g. Set rng = Range("'John''s Sheet'!C3,'John''s
Sheet'!D3,'John''s
Sheet'!F1,'John''s Sheet'!G1"). Yuck. That is probably not an issue
for
you,
and I debated about whether or not to add what you may think is "too
much
information" (?).. But your extensive questioning and usage of
Addresses
makes me wonder if you are going to want to set a range object equal
to
the
address. Another example:

Dim SheetName as String
Dim rng as Range
ActiveSheet.Name = "John's Sheet"
SheetName = ActiveSheet.Name
' This works
Set rng = Range("'" & Replace(SheetName, "'", "''") & "'!A2")
' This doesn't work:
Set rng = Range(SheetName & "!A2")

I am assuming that Union does not work on multiple sheets. How
would
you
do
this if you had references that were on multiple sheets? Assuming
quick
answer is to have separate range objects for each sheet.

Yep, as far as I know, it's not possible to reference more than one
sheet
at
a time.


--
Tim Zych
SF, CA
"ExcelMonkey" wrote in message
...
Thanks. That does it. Two last questions

Question 1
I edited the code that when printing to the immediate window is also
shows
the Sheet name as wel as the cell address. I further want to set it
up
so
that instead of printing results as:

Sheet1!C3:D3,F1:G1

It would print as follows:

Sheet1!C3:D3,
Sheet1!F1:G1

How would you do this? Assuming its the union that bring them
together
in
one line item.

Question 2
I am assuming that Union does not work on multiple sheets. How
would
you
do
this if you had references that were on multiple sheets? Assuming
quick
answer is to have separate range objects for each sheet. Is it
possible
to
do it without having separate range objects?

Sub CountAreas()
Dim rng As Range
Dim rngArea As Range
Dim rngUnion As Range
Set rng = Range("Sheet1!C3,Sheet1!D3,Sheet1!F1,Sheet1!G1")
Set rngUnion = rng.Areas(1)
For Each rngArea In rng.Areas
Set rngUnion = Union(rngUnion, rngArea)
Next
Debug.Print rngUnion.Parent.Name & "!" & rngUnion.Address(0, 0) '
or
rng.Address(0,0)
End Sub




"Tim Zych" wrote:

I see what you are saying.

Range("C3:D3") is contiguous
Range("C3, D3") is not contiguous as Excel sees it. From the help
file,
Excel refers to contiguousness as it pertains to the areas.count,
and
makes
a distinction there. To work around that:

Sub CountAreas()

Dim rng As Range
Dim rngArea As Range
Dim rngUnion As Range
Set rng = Range("C3,D3")
Set rngUnion = rng.Areas(1)
For Each rngArea In rng.Areas
Set rngUnion = Union(rngUnion, rngArea)
Next
If rngUnion.Areas.Count = 1 Then
Debug.Print "Range can form 1 area."
Else
Debug.Print "More than 1 area - not contiguous."
End If
'Debug.Print rngUnion.Areas.Count
Debug.Print rngUnion.Address(0, 0) ' or rng.Address(0,0)

End Sub





--
Tim Zych
SF, CA

"ExcelMonkey" wrote in
message
...
The example you show should be a contiguous range but the ouput
suggests
the
opposite. Should this not return a "1" and not a "2"?

Sub Address()
Dim rng As Range
Set rng = Range("C3,D3")
Debug.Print rng.Areas.Count
Debug.Print rng.Address(0, 0)
End Sub

Immediate Window: