Microsoft Office Tutorials and References
In Depth Information
Figure 24-2: Use the Intersect function to work with the intersection of two ranges.
The CELLSINCOMMON function uses the Intersect function to create a range object named
CommonCells . Note the use of On Error Resume Next . This statement is necessary
because the Intersect function returns an error if the ranges have no cells in common. If the
error occurs, it is ignored. The final statement checks the Number property of the Err object.
If it is 0 , no error occurs, and the function returns the value of the CountLarge property for the
CommonCells object. If an error does occur, Err.Number has a value other than 0 , and the
function returns 0 .
The Union function
The Union function combines two or more ranges into a single range. The following statement
uses the Union function to create a range object that consists of the first and third columns of a
Set TwoCols = Union(Range(“A:A”), Range(“C:C”))
The Union function takes between 2 and 30 arguments.
The UsedRange property
The UsedRange property returns a Range object that represents the used range of the
worksheet. Press Ctrl+End to activate the lower-right cell of the used range. The UsedRange property
can be very useful in making your functions more efficient.