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

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

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

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

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

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

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

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:
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

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:
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

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:
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

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:

Tim, apologies for the late repsonse to this thread. One last question. The
problem I am going to run into here is that I am dumping an array filled with
cell addresses into the range object. As per your earlier post, I will have
a limit on the total string length in the range object. So how do I get
around this? For Example you may get something like this:

Range("A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21,C1,C 3,C5,C7,C9,C11,C13,C15,C17,C19,C21,E1,E3,E5,E7,E9, E11,E13,E15,E17,E19,E21,G1,G3,G5,G7,G9,G11,G13,G15 ,G17,G19,G21,I1,I3,I5,I7,I9,I11,I13,I15,I17,I19,I2 1,K1,K3,K5,K7,K9,K11,K13,K15,K17,K19,K21,M1,M3,M5, M7,M9,M11,M13,M15,M17,M19,M21,A2")

1) Can you put error handling in that creates new range objects when needed
and continue to dump from the array into these successive range objects?

2) Even if you can do this, there is a chance that multiple range objects
will contain cells which are contigous. For example range1 and range2 may
have addresses in each which are contiguous (See Below: A1 and A2). Would
need a final test which tries to perform a union on the multiple range
objects created to ensure that nothing was missed. How do you do this?

Range1("A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21,C1, C3,C5,C7,C9,C11,C13,C15,C17,C19,C21,E1,E3,E5,E7,E9 ,E11,E13,E15,E17,E19,E21,G1,G3,G5,G7,G9,G11,G13,G1 5,G17,G19,G21,I1,I3,I5,I7,I9")
Range2(I11,I13,I15,I17,I19,I21,K1,K3,K5,K7,K9,K11, K13,K15,K17,K19,K21,M1,M3,M5,M7,M9,M11,M13,M15,M17 ,M19,M21,A2")
Regards

Thanks

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: