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