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:
2
C3,D3

"Tim Zych" wrote:

When Areas.Count = 1, the range is contiguous.

Dim rng as Range
Set rng = Range("C3, D3")
debug.print rng.areas.count
debug.print rng.Address(0,0)



--
Tim Zych
SF, CA

"ExcelMonkey" wrote in message
...
Is there a way in VBA to ascertain if a range selection is
contiguous.
That
is, say I select C3 and D4 on the same page. The Address property
for
the
Selection does not automatically put the address into the form C3:D3
but
instead into the form $C$3,$D$3. Is there a way to do this in VBA
or
do
I
have to write a function which tests the string for contiguous row
or
column
headers?

Sub Macro1()
Dim RngAddress As String
Range("C3,D3").Select
RngAddress = Selection.Address
Debug.Print RngAddress
End Sub

Immediate Window:
$C$3,$D$3

Thanks

EM