opposite of intersect

[David C.]

hello
In Excel VBA, I've got a range from wich I would like to "except a cell"

example: R = A:A
and I would like R=A:A except A3
this means R = union(A1:A2;A4:A65536)

in the general way, how is it possible ?
I know union, I know intersect,
how to do this one... ?

 

[Jim Rech]

You cannot remove a cell from a range object as far as I know. All you can
do is build a new range based on another but not including the excluded
cell.

Here is an example of one way to do this. I'm not sure it's practical for
such a large range as in your example:

Sub TestIt()
AntiRange(Range("A1:F100"), Range("B1:B10")).Select
End Sub

Function AntiRange(BigRg As Range, ExcludeRg As Range) As Range
Dim NewRg As Range, CurrCell As Range
For Each CurrCell In BigRg.Cells
If Intersect(CurrCell, ExcludeRg) Is Nothing Then
If NewRg Is Nothing Then
Set NewRg = CurrCell
Else
Set NewRg = Union(NewRg, CurrCell)
End If
End If
Next
Set AntiRange = NewRg
End Function

--
Jim Rech
Excel MVP
| hello
| In Excel VBA, I've got a range from wich I would like to "except a cell"
|
| example: R = A:A
| and I would like R=A:A except A3
| this means R = union(A1:A2;A4:A65536)
|
| in the general way, how is it possible ?
| I know union, I know intersect,
| how to do this one... ?
|
|
|

 

[dcdc2]

thank you, i also think it's not great for big ranges

for big one,
maybe working on row and column of the exctracted cell

well, there's no except build-in function, that's the answer...