I've never seen that splitting of the range address.

It's my own invention.<g The idea is to get the last address in the range,
but to protect against that range being a single cell (where there is no
colon in the address to Split on).

--
Rick (MVP - Excel)


"John" wrote in message
...
That works for me. thanks
For some reason you can't use Findnext so I use the Find(After=) and stop
when it = First.

I've never seen that splitting of the range address.
Thanks again


John



Rick Rothstein wrote:
Here is same code, but modified slightly to eliminate the overhead
associated with iterating a range of cells... I changed the loop from a
For Each to an "normal" For..Next loop. I also changed the method of
calculating the initial cell reference assigned to the Found variable
(since the first method I used would not always calculate the last cell
in the the range assigned to Rng correctly)

Sub Test()
Dim X As Long
Dim Rng As Range
Dim Found As Range
Dim AllSize18Cells As Range
Dim FirstRange As String
Set Rng = Range("A24")
' Start with the last cell in the range so that
' the addresses will list in ascending order.
Set Found = Range(Split(Rng.Address & ":" & Rng.Address, ":")(1))
Application.FindFormat.Font.Size = 18
For X = 1 To Rng.Count
Set Found = Rng.Find(After:=Found, What:="", MatchCase:=False, _
LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, LookAt:=xlPart, _
SearchFormat:=True)
If Found.Address = FirstRange Then Exit For
If AllSize18Cells Is Nothing Then
Set AllSize18Cells = Found
FirstRange = Found.Address
Else
Set AllSize18Cells = Union(AllSize18Cells, Found)
End If
Next
MsgBox AllSize18Cells.Address
End Sub