VBA - Determine if one range is within another?

[mothra]

What's the best way to find out if one range is within another? For example...

is "A6:A10" within "A1:A20" ......(yes)
is "B8" within "A1:B4" ...............(no)


Hoping to do this without loops.

 

[Richard Schollar]

I think it depends on whether you can accept knowing that at least one cell in each range overlaps or if only knowing if all cells in one of the ranges is contained in the other will do. The former:

If Not Intersect(Range1,Range2) Is Nothing Then
  'they overlap at least partially
Else
  'They don't overlap at all
End If

 

[xenou]

Sub test()
If Not Intersect(Range("A6:A10"), Range("A1:A20")) Is Nothing Then
    MsgBox ("Range A6:A10 intersects Range A1:A20")
End If

If Intersect(Range("B8"), Range("A1:B4")) Is Nothing Then
    MsgBox ("Range B8 does not intersects Range A1:B4")
End If

End Sub

You have to be careful with the If or If Not's - as above. If the ranges intersect, the returned object is not Nothing ...

Regards.

 

[Richard Schollar]

For whole overlaps you could perhaps use:

Set rOverlap = Intersect(Range1,Range2)
If Not rOverlap Is Nothing Then  'ie is there some overlap?
  If rOverlap.Count = Range1.Count Or rOverlap.Count = Range2.Count Then
    'one range is fully contained within the second range
  Else
    'ranges only partially overlap
  End If
Else
  'Ranges don'toverlap at all
End If

The above does assume that Range1 and Range2 are both contiguous ranges.

 

[pgc01]

Hi

Another one, with all the cases

    If Intersect(r1, r2) Is Nothing Then
        MsgBox "r1 and r2 do not intersect"
    ElseIf r1.Address = r2.Address Then
        MsgBox "r1 and r2 overlap completely"
    ElseIf Intersect(r1, r2).Address = r2.Address Then
        MsgBox "r2 is completely contained in r1"
    ElseIf Intersect(r1, r2).Address = r1.Address Then
        MsgBox "r1 is completely contained in r2"
    ElseIf Not Intersect(r1, r2) Is Nothing Then
        MsgBox "r1 and r2 intersect"
    End If

The last condition is not necessary.

 

[Gates Is Antichrist]

Why not just For Each c in one range, test intersect against the other? (dim c as range)

 

[Gates Is Antichrist]

Answering last rhetorical spew: because the previous two answers are much better :bangHead: Never mind to my post! Doooh!

 

[mothra]

Thanks all. So far the first example seems to suit my needs.