RangeExclude function

[R Avery]

Has anyone written a good, fast function that takes two range arguments,
and returns a range variable equal to the first range excluding the
intersection of the first and second ranges. The function I have
written is very basic and slow.

I would be very interested if there is a faster version of this
function. Thanks in advance.








Public Function RangeExclusion(FromRange As Excel.Range, ExcludeRange As
Excel.Range) As Excel.Range
Dim rngCell As Excel.Range, rngAnswer As Excel.Range

If ExcludeRange Is Nothing Then
Set RangeExclusion = FromRange
Exit Function
ElseIf FromRange Is Nothing Then
Set RangeExclusion = Nothing
Exit Function
End If

For Each rngCell In FromRange
If Application.Intersect(rngCell, ExcludeRange) Is Nothing Then
If rngAnswer Is Nothing Then
Set rngAnswer = rngCell
Else
Set rngAnswer = Union(rngAnswer, rngCell)
End If
End If
Next

Set RangeExclusion = rngAnswer

' Clean up.
Set rngCell = Nothing
Set rngAnswer = Nothing
End Function

 

[Bob Phillips]

'-----------------------------------------------------------------
Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
'-----------------------------------------------------------------
Dim saveSet
saveSet = SetRange.Formula
SetRange.ClearContents
UsedRange = 0
Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
SetRange = saveSet
End Function

 

[Bernie Deitrick]

Jim Rech has posted this previously:

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

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Bob,

I like that - though when I was thinking of ways to do this with a function,
I dismissed your apporach, with the thought that "Functions can't operate on
ranges, they can only return values" but it works quite nicely.

Thanks,
Bernie
Maybe/Maybe Not MS Excel MVP

 

[R Avery]

That is pretty sexy. I just wish I could then undo the effect that your
macro had on the range, and it would be perfect =P

 

[Jim Rech]

Maybe/Maybe Not MS Excel MVP

Check the MVP site for the latest MVP NG password<g>.

Doesn't work too well if there are array formulas in the range since it
doesn't array-enter them on the restore. Also, would you want to set calc
to manual?

--
Jim Rech
Excel MVP
| Bob,
|
| I like that - though when I was thinking of ways to do this with a
function,
| I dismissed your apporach, with the thought that "Functions can't operate
on
| ranges, they can only return values" but it works quite nicely.
|
| Thanks,
| Bernie
| Maybe/Maybe Not MS Excel MVP
|
| | > '-----------------------------------------------------------------
| > Function AntiUnion(SetRange As Range, UsedRange As Range) As Range
| > '-----------------------------------------------------------------
| > Dim saveSet
| > saveSet = SetRange.Formula
| > SetRange.ClearContents
| > UsedRange = 0
| > Set AntiUnion = SetRange.SpecialCells(xlCellTypeBlanks)
| > SetRange = saveSet
| > End Function
| >
| >
| > --
| >
| > HTH
| >
| > RP
| >
| > | > > Has anyone written a good, fast function that takes two range
arguments,
| > > and returns a range variable equal to the first range excluding the
| > > intersection of the first and second ranges. The function I have
| > > written is very basic and slow.
| > >
| > > I would be very interested if there is a faster version of this
| > > function. Thanks in advance.
| > >
| > >
| > >
| > >
| > >
| > >
| > >
| > >
| > > Public Function RangeExclusion(FromRange As Excel.Range, ExcludeRange
As
| > > Excel.Range) As Excel.Range
| > > Dim rngCell As Excel.Range, rngAnswer As Excel.Range
| > >
| > > If ExcludeRange Is Nothing Then
| > > Set RangeExclusion = FromRange
| > > Exit Function
| > > ElseIf FromRange Is Nothing Then
| > > Set RangeExclusion = Nothing
| > > Exit Function
| > > End If
| > >
| > > For Each rngCell In FromRange
| > > If Application.Intersect(rngCell, ExcludeRange) Is Nothing
Then
| > > If rngAnswer Is Nothing Then
| > > Set rngAnswer = rngCell
| > > Else
| > > Set rngAnswer = Union(rngAnswer, rngCell)
| > > End If
| > > End If
| > > Next
| > >
| > > Set RangeExclusion = rngAnswer
| > >
| > > ' Clean up.
| > > Set rngCell = Nothing
| > > Set rngAnswer = Nothing
| > > End Function
| >
| >
|
|

 

[Bernie Deitrick]

Jim,

I just did (though it took some time to find the link) so now I can leave my
signature line alone <vbg>, and I'm guessing you can, too.

I didn't do a lot of testing, just once with a blank worksheet, and was
simply surprised to see that it worked at all. So much for depending on the
stated limitations of U-D-Functions.

Of course, in a sub it would be easy to get around all those other
limitations by simply adding a blank worksheet, doing the 'disunion' on the
blank sheet, recording the address string, and then deleting the worksheet.

Bernie
Still a MS Excel MVP

 

[Bob Phillips]

Do you want a UDF or a VBA function?

 

[Jim Rech]

So much for depending on the stated limitations of U-D-Functions.

The limitations (not affecting other cells, not taking actions, etc.) apply
only when the VB function is called from a formula, i.e. a worksheet cell.
MS doesn't want a formulas triggering a change in the recalc chain, much
less a file save in the middle of a calc<g>.


Jim Rech
Excel MVP

 

[R Avery]

VBA function