Microsoft Office Tutorials and References

In Depth Information

**Using Ranges**

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

worksheet:

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.