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

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

"R Avery" wrote in message
...
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,

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

"Bob Phillips" wrote in message
...
'-----------------------------------------------------------------
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

"R Avery" wrote in message
...
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

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
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
| 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
|
| "Bob Phillips" wrote in message
| ...
| '-----------------------------------------------------------------
| 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
|
| "R Avery" wrote in message
| ...
| 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
|
|
|
|

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

"Jim Rech" wrote in message
...
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
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
| 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
|
| "Bob Phillips" wrote in message
| ...
| '-----------------------------------------------------------------
| 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
|
| "R Avery" wrote in message
| ...
| 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
|
|
|
|

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
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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

"Jim Rech" wrote in message
...
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
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
| 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
|
| "Bob Phillips" wrote in message
| ...
| '-----------------------------------------------------------------
| 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
|
| "R Avery" wrote in message
| ...
| 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
|
|
|
|

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

Do you want a UDF or a VBA function?

--

HTH

RP

"R Avery" wrote in message
...
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

VBA function

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

"R Avery" wrote in message
...
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