Thanks:  0
Likes:  0

# Thread: Defining a through Range Difference

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

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

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

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"))
End Sub
```

4. Thank you Ivan.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•