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 rng1 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:

rng1.Parent.Name

The following expression returns the name of the workbook for rng1 : rng1.Parent.Parent.Name

VBA's Union function returns a Range object that represents the union of two Range objects. The union consists of all the cells from both ranges. 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. CD-ROM

A workbook that contains this function is available on the companion CD-ROM in a file named ■*■ inrange function.xlsm .

0 0

Post a comment