Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... ? | | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Intersect Line | Charts and Charting in Excel | |||
Intersect with decimal | Excel Programming | |||
Dont intersect | Excel Programming | |||
Intersect Formula??? | Excel Programming | |||
Help with If Not Intersect | Excel Programming |