Number of colons

[Gary''s Student]

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

 

[Chip Pearson]

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 B510")
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)

 

[JLGWhiz]

Huh?

 

[Peter T]

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

 

[Gary''s Student]

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

 

[Ron Rosenfeld]

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

 

[Peter T]

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("C34, 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

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]

Thanks!
--
Gary''s Student - gsnu200793

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

 

[Gary''s Student]

Thanks!
--
Gary''s Student - gsnu200793

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("C34, 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