opposite of Union

[mark]

Hi.

I have a range name which defines a bunch of discontiguous cells... probably
100 or more.

Unfortunately, I have one cell, BR8, which is included in this defined range
name, which should not be.

I've tried getting access to the address of all of the cells defined by this
range name, so that I can have a program select all but that one, to redefine
the range name.

But, the Selection.Address property, and the Names(i).RefersTo property only
provide a small portion of the actual list of addresses involved.

What I'd like is the opposite of a Union..... sort of like an
OppositeUnion(rgRange,"BR8"), which would deselect BR8 from the selection, so
that I could then rename the proper set of cells.

I could go through and manually re-select all of the proper cells, but is
there an easier way?

Thanks,
Mark

 

[Héctor Miguel]

hi, Mark !

i'm afraid you need to "divorce" (or slice) that cell/range and make another union
(i.e.) using range objects (or adapt/modify/... to work with its address) -?-

Private Function Slice(Where As Range, Which As Range) As Range
Dim xCell As Range
For Each xCell In Where
If Intersect(xCell, Which) Is Nothing Then _
Set Slice = Union(IIf(Slice Is Nothing, xCell, Slice), xCell)
Next
End Function

hth,
hector.

__ OP __

 

[Dave Peterson]

Check your other post.

Hi.

I have a range name which defines a bunch of discontiguous cells... probably
100 or more.

Unfortunately, I have one cell, BR8, which is included in this defined range
name, which should not be.

I've tried getting access to the address of all of the cells defined by this
range name, so that I can have a program select all but that one, to redefine
the range name.

But, the Selection.Address property, and the Names(i).RefersTo property only
provide a small portion of the actual list of addresses involved.

What I'd like is the opposite of a Union..... sort of like an
OppositeUnion(rgRange,"BR8"), which would deselect BR8 from the selection, so
that I could then rename the proper set of cells.

I could go through and manually re-select all of the proper cells, but is
there an easier way?

Thanks,
Mark

 

[mark]

Thanks, guys. I got it working from looping through the cells, as suggested
in the other post.

Sorry about the double post, I know that's usually bad form.

I tried to post this first one just before leaving work yesterday, but then
a couple hours later at home, I didn't see this post.... not sure what
happened to it. Maybe I just missed it.

Anyway, since I couldn't find this one, I posted the second one.

Thanks for the help.