Intersecting Ranges

The Intersect function can be used to determine if a cell is part of another range. Intersect returns a Range object representing the range where the arguments intersect. For instance, to determine if the cell G10 exists in the range named MyRange, use code like this

If Not Intersect(Range(“G10?), Range(“MyRange”)) Is Nothing Then

This will return True if G10 is a part of MyRange, and False if it’s not. That works well when one of the arguments is a single-cell range. If you have a couple of multi-cell ranges, and you want to determine if one range is completely contained in a smaller range, use

bContained = False
On Error Resume Next
    bContained = Intersect(Range(“G10:G12?), Range(“MyRange”)).Address = _
        Range(“G10:G12?).Address
On Error GoTo 0

There are three possible outcomes: If the ranges don’t intersect at all, then Intersect().Address will return an error and the Boolean variable will remain False; if only part of the smaller range is in the bigger range, then the Address of the range returned by Intersect won’t be the same as the smaller range and the Boolean will be False; and if the smaller range is completely within the bigger range, the Addresses will be exactly the same. This method will, of course, work just as well with single-cell ranges, but it’s a little longer.

Posted in Uncategorized


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.