Number of colons
 

Is is safe to assume that if the address of a range does not contain a comma,
that it can contain at most one colon??
--
Gary''s Student - gsnu200793

Number of colons
 

It is not safe to make that assumption. The space character in an address
indicates Intersection, so you could have an address with two colons and no
commas.


Set R = Range("A1:C5 B5:D10")
Debug.Print R.Address ' displays B5:C5


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Gary''s Student" wrote in message
...
Is is safe to assume that if the address of a range does not contain a
comma,
that it can contain at most one colon??
--
Gary''s Student - gsnu200793

Number of colons
 

Huh?

"Gary''s Student" wrote:

Is is safe to assume that if the address of a range does not contain a comma,
that it can contain at most one colon??
--
Gary''s Student - gsnu200793

Number of colons
 

Another one -

Set r = Range("a3:c2:e3:e4:a6:a1")
Debug.Print r.Address(0, 0) ' A1:E6

Although you can make a range with such an address you are probably right
that any returned address that does not contain a comma will contain at most
one colon, ie a single area.

Regards,
Peter T

"Chip Pearson" wrote in message
...
It is not safe to make that assumption. The space character in an address
indicates Intersection, so you could have an address with two colons and
no
commas.


Set R = Range("A1:C5 B5:D10")
Debug.Print R.Address ' displays B5:C5


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Gary''s Student" wrote in
message
...
Is is safe to assume that if the address of a range does not contain a
comma,
that it can contain at most one colon??
--
Gary''s Student - gsnu200793

Number of colons
 

Thank you Peter & Chip:

I am trying to help a buddy speed up a very slow macro. Part of her macro
locates the value in the last cell in a range. She does this by looping thru
the range. This MIGHT be necessary if the range is dis-joint.

If, however, the range is not dis-joint (so there are no commas in the
address), then no loop should be needed. She can just SPLIT() the address on
colon and pickup the second element as the address of the terminal item.

Thanks again
--
Gary''s Student - gsnu200793


"Peter T" wrote:

Another one -

Set r = Range("a3:c2:e3:e4:a6:a1")
Debug.Print r.Address(0, 0) ' A1:E6

Although you can make a range with such an address you are probably right
that any returned address that does not contain a comma will contain at most
one colon, ie a single area.

Regards,
Peter T

"Chip Pearson" wrote in message
...
It is not safe to make that assumption. The space character in an address
indicates Intersection, so you could have an address with two colons and
no
commas.


Set R = Range("A1:C5 B5:D10")
Debug.Print R.Address ' displays B5:C5


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Gary''s Student" wrote in
message
...
Is is safe to assume that if the address of a range does not contain a
comma,
that it can contain at most one colon??
--
Gary''s Student - gsnu200793

Number of colons
 

On Wed, 18 Jun 2008 13:18:00 -0700, Gary''s Student
wrote:

Thank you Peter & Chip:

I am trying to help a buddy speed up a very slow macro. Part of her macro
locates the value in the last cell in a range. She does this by looping thru
the range. This MIGHT be necessary if the range is dis-joint.

If, however, the range is not dis-joint (so there are no commas in the
address), then no loop should be needed. She can just SPLIT() the address on
colon and pickup the second element as the address of the terminal item.


You could also get the address of the last cell in a contiguous range with :

debug.pring rg(rg.rows.count,rg.columns.count).address

Or, if there are multiple areas:

rg.Areas(rg.areas.count)(...
--ron

Number of colons
 

Depends what you mean by "last cell". Following returns the last cell of
each area and the bottom right cell which might not exist in any of the
areas at all.

Sub abc()
Dim ra As Range
Dim nR As Long, nC As Long

Set rng = Range("C3:D4, A1:A2, B6:B10, F1")
For Each ra In rng.Areas
With ra
With .Cells(.Count)
If .Row nR Then nR = .Row
If .Column nC Then nC = .Column
Debug.Print .Address(0, 0)
End With
End With
Next

Debug.Print "last cell " & Cells(nR, nC).Address ' F10
End Sub


"Gary''s Student" wrote in message
...
Thank you Peter & Chip:

I am trying to help a buddy speed up a very slow macro. Part of her macro
locates the value in the last cell in a range. She does this by looping
thru
the range. This MIGHT be necessary if the range is dis-joint.

If, however, the range is not dis-joint (so there are no commas in the
address), then no loop should be needed. She can just SPLIT() the address
on
colon and pickup the second element as the address of the terminal item.

Thanks again
--
Gary''s Student - gsnu200793


"Peter T" wrote:

Another one -

Set r = Range("a3:c2:e3:e4:a6:a1")
Debug.Print r.Address(0, 0) ' A1:E6

Although you can make a range with such an address you are probably
right
that any returned address that does not contain a comma will contain at
most
one colon, ie a single area.

Regards,
Peter T

"Chip Pearson" wrote in message
...
It is not safe to make that assumption. The space character in an
address
indicates Intersection, so you could have an address with two colons
and
no
commas.


Set R = Range("A1:C5 B5:D10")
Debug.Print R.Address ' displays B5:C5


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Gary''s Student" wrote in
message
...
Is is safe to assume that if the address of a range does not contain
a
comma,
that it can contain at most one colon??
--
Gary''s Student - gsnu200793

Number of colons
 

Thanks!
--
Gary''s Student - gsnu200793


"Ron Rosenfeld" wrote:

On Wed, 18 Jun 2008 13:18:00 -0700, Gary''s Student
wrote:

Thank you Peter & Chip:

I am trying to help a buddy speed up a very slow macro. Part of her macro
locates the value in the last cell in a range. She does this by looping thru
the range. This MIGHT be necessary if the range is dis-joint.

If, however, the range is not dis-joint (so there are no commas in the
address), then no loop should be needed. She can just SPLIT() the address on
colon and pickup the second element as the address of the terminal item.


You could also get the address of the last cell in a contiguous range with :

debug.pring rg(rg.rows.count,rg.columns.count).address

Or, if there are multiple areas:

rg.Areas(rg.areas.count)(...
--ron

Number of colons
 

Thanks!
--
Gary''s Student - gsnu200793


"Peter T" wrote:

Depends what you mean by "last cell". Following returns the last cell of
each area and the bottom right cell which might not exist in any of the
areas at all.

Sub abc()
Dim ra As Range
Dim nR As Long, nC As Long

Set rng = Range("C3:D4, A1:A2, B6:B10, F1")
For Each ra In rng.Areas
With ra
With .Cells(.Count)
If .Row nR Then nR = .Row
If .Column nC Then nC = .Column
Debug.Print .Address(0, 0)
End With
End With
Next

Debug.Print "last cell " & Cells(nR, nC).Address ' F10
End Sub


"Gary''s Student" wrote in message
...
Thank you Peter & Chip:

I am trying to help a buddy speed up a very slow macro. Part of her macro
locates the value in the last cell in a range. She does this by looping
thru
the range. This MIGHT be necessary if the range is dis-joint.

If, however, the range is not dis-joint (so there are no commas in the
address), then no loop should be needed. She can just SPLIT() the address
on
colon and pickup the second element as the address of the terminal item.

Thanks again
--
Gary''s Student - gsnu200793


"Peter T" wrote:

Another one -

Set r = Range("a3:c2:e3:e4:a6:a1")
Debug.Print r.Address(0, 0) ' A1:E6

Although you can make a range with such an address you are probably
right
that any returned address that does not contain a comma will contain at
most
one colon, ie a single area.

Regards,
Peter T

"Chip Pearson" wrote in message
...
It is not safe to make that assumption. The space character in an
address
indicates Intersection, so you could have an address with two colons
and
no
commas.


Set R = Range("A1:C5 B5:D10")
Debug.Print R.Address ' displays B5:C5


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Gary''s Student" wrote in
message
...
Is is safe to assume that if the address of a range does not contain
a
comma,
that it can contain at most one colon??
--
Gary''s Student - gsnu200793