Deriving the remainder of a range intersection (97sr2)

Topic

New Reply

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,
In SQL I suppose it would be similar to
select * from A where A not in (select * from

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

Reply | Quote

Replies

Afraid not, see for example this newsgroup post.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Afraid not, see for example this newsgroup post.

Reply | Quote