Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Defining a through Range Difference

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Belgium
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    New Member
    Join Date
    Feb 2002
    Location
    Belgium
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Belgium
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you Ivan.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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