"Split Unioned Ranges"

[CG Rosén]

Good Day Group,

Is there another way to "split" the "result" -variable from the code below
into variables of type:
r = Range("C2:F2") etc.
With my limited knowledge I can only think of do it with Len and Mid
functions.

Brgds

CG Rosén
---------------------------------------------------------------
Dim rng As Range, cell As Range
Dim rngList() As Range, i As Long

For Each cell In Rows(3).Cells
If cell.Interior.ColorIndex = 15 Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next

If Not rng Is Nothing Then
result = rng.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End If

MsgBox result

 

[Bill Manville]

Is there another way to "split" the "result" -variable from the code below
into variables of type:

Dim R As Range
For Each R In Rng.Areas
MsgBox R.Address
Next


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup