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