Determining whether a range is contained in another range

The following InRange function accepts two arguments, both Range objects. The function returns True if the first range is contained in the second range.

Function InRange(rng1, rng2) As Boolean ' Returns True if rngl is a subset of rng2 InRange = False

If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then If rng1.Parent.Name = rng2.Parent.Name Then

If Union(rng1, rng2).Address = rng2.Address Then

InRange = True End If End If End If End Function

The InRange function may appear a bit more complex than it needs to be because the code needs to ensure that the two ranges are in the same worksheet and workbook. Notice that the procedure uses the Parent property, which returns an object's container object. For example, the following expression returns the name of the worksheet for the rng1 object reference:


The following expression returns the name of the workbook for rng1:


VBA's Union function returns a Range object that represents the union of two Range objects. The union is the cells that the two ranges have in common. If the address of the union of the two ranges is the same as the address of the second range, the first range is contained within the second range.

0 0

Post a comment