opposite of intersect
 

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... ?

opposite of intersect
 

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
"David C." wrote in message
...
| 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... ?
|
|
|

opposite of intersect
 

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...