Defining a through Range Difference

[paludgnp]

Hello,

I am looking for the code to define a Range as a function of the 'difference' of two others (the opposite of the union method, but not the intersect)
set NewRange = BigRange1 - Range2
br,
Pierre

[paludgnp]

Since nobody has replied yet to sth I expected to be easy, I will reformulate my demand:
Let's say I have a sheet with data and titles in the first row. I would like to select the data area in order to copy it on another sheet. For this I expect a code as Union(range1,range2) or Intersect(range1,range2) but it would make the difference (Diff).
In my case:

set MyRange= Diff(cells(1,1).currentregion, rows(1))

Is there in VBA such a function as the Diff I defined above?
If not I know I can use:

set MyRange= Intersect(cells(1,1).currentregion, range(cells(1,1),cells(10000,1000))

It will give the same result but I think the code is less 'clean'.

br,
Pierre

[Ivan F Moala]

I don't believe there is a built in function
to do this (If I understand you correctly)

But you could try this....

Function NotUnion(Rg1 As Range, Rg2 As Range) As Range

'// Original by Tom Ogilvy

Dim Rg3 As Range

Dim Rg As Range

Dim oCell As Range



If Rg1.Address = Rg2.Address Then

  Set NotUnion = Nothing

  Exit Function

End If

Set Rg = Intersect(Rg1, Rg2)

  If Rg Is Nothing Then

    Set NotUnion = Union(Rg1, Rg2)

    Exit Function

  End If

For Each oCell In Union(Rg1, Rg2)

  If Intersect(oCell, Rg) Is Nothing Then

    If Rg3 Is Nothing Then

      Set Rg3 = oCell

    Else

      Set Rg3 = Union(Rg3, oCell)

    End If

  End If

Next

Set NotUnion = Rg3

End Function



Sub test()

Dim r

Set r = NotUnion(Range("A1:A10"), Range("A1:H1"))

MsgBox r.Address

End Sub

[paludgnp]

Thank you Ivan.