Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deriving the remainder of a range intersection (97sr2)

    Hi all,

    I am not sure if there is an answer to this.
    I am trying to process two range objects A & B to derive a third range C that is equivalent to the remainder of range A not in range B.

    For example.
    Range A = UNION(A1:A5, A8:A10, A17)
    Range B = A4:A9
    Range C should be then UNION(A1:A3, A10, A17)

    A bit like a NOT_INTERSECT(A, [img]/forums/images/smilies/cool.gif[/img]
    In SQL I suppose it would be similar to
    select * from A where A not in (select * from [img]/forums/images/smilies/cool.gif[/img]

    I know I can achieve this by looping through each component of the ranges, testing for intersections and slowly building up range C when the intersection is nothing.
    I would like to know if anyone has a method of constructing the range using a faster/more efficient method. I cannot see how to do it with the UNION or INTERSECT methods, or any of the worksheet functions.

    For my problem, range B will always have the same number of columns as range A

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deriving the remainder of a range intersection (97sr2)

    Afraid not, see for example this newsgroup post.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deriving the remainder of a range intersection (97sr2)

    Thanks,

    That confirms my suspicions.
    Chip's routine looks like it may be fairly quick anyway.

    So I have ended up with this :


    Function RngA_NotIn_B_ByRow(A As Range, B As Range) As Range
    ' will return the rows in A that do not intersect with B

    ' based on a function (RangeDiff) by Chip Pearson

    ' assumes that A and B have the same start & end columns.
    ' does not require the ranges to be contiguous

    Dim Res As Range
    Dim RngArea As Range
    Dim I As Long
    Dim ColCount As Integer
    Dim WorkRow As Range

    ColCount = A.Columns.Count

    For Each RngArea In A.Areas

    With RngArea
    If Application.Intersect(RngArea, [img]/forums/images/smilies/cool.gif[/img] Is Nothing Then
    ' no intersection means wole of area can be added to result
    If Res Is Nothing Then
    Set Res = RngArea
    Else
    Set Res = Application.Union(Res, RngArea)
    End If
    Else ' add relevant rows one by one
    For I = 1 To .Rows.Count
    ' need only to test one cell in each row as columns will be identical
    Set WorkRow = Range(.Cells(I, 1), .Cells(I, ColCount))
    If Application.Intersect(.Cells(I, 1), [img]/forums/images/smilies/cool.gif[/img] Is Nothing Then
    If Res Is Nothing Then
    Set Res = WorkRow
    Else
    Set Res = Application.Union(Res, WorkRow)
    End If
    End If
    Next I
    End If
    End With

    Next RngArea

    Set RngA_NotIn_B_ByRow = Res

    Set Res = Nothing
    Set RngArea = Nothing
    Set WorkRow = Nothing

    End Function

Posting Permissions

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