You could loop through the cells:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim myRngToSkip As Range

With Worksheets("sheet1")
'add/subtract addresses here!
Set myRngToSkip = .Range("BR8,F6")

For Each myCell In .Range("Name1").Cells
If Intersect(myCell, myRngToSkip) Is Nothing Then
If myRng Is Nothing Then
Set myRng = myCell
Else
Set myRng = Union(myRng, myCell)
End If
Else
'skip it, it's in the range to skip
End If
Next myCell

If myRng Is Nothing Then
MsgBox "No cells!"
Else
myRng.Select 'just for testing!
myRng.Name = "Name2" 'or even Name1 if you want to reuse that name.
End If
End With

End Sub



mark wrote:

Hi.

I have a range name that defines a LOT of discontinuous cells... probably
more than 100.

But unfortunately, there is one cell, BR8 , included in this range name
definition, which should not be.

I've used the .Address and the .RefersTo properties to try to get the
addresses of all of the cells define. But, the length of the properties must
not be long enough to handle the definitions.

I can use F5 and go to the discontinuous range, but I need a way to
"deselect" the one cell that should not be there, so that I can then redefine
the range name to be the current selection.

Help?

Thanks.

--

Dave Peterson