Microsoft Office Tutorials and References
In Depth Information
Figure 25-2: Use the Intersect function to work with the intersection of two ranges.
The following Function procedure accepts two range arguments and returns the count of the number of cells
that the ranges have in common:
Function CELLSINCOMMON(rng1, rng2)
Dim CommonCells As Range
On Error Resume Next
Set CommonCells = Intersect(rng1, rng2)
If Err.Number = 0 Then
CELLSINCOMMON = CommonCells.CountLarge
CELLSINCOMMON = 0
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 er-
ror if the ranges have no cells in common. If the error occurs, it is ignored. The final statement checks the Num-
ber 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 func-
tion 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.
Consider the following Function procedure. This function accepts a range argument and returns the number of
formula cells in the range:
cnt = 0
For Each cell In rng
If cell.HasFormula Then cnt = cnt + 1
FORMULACOUNT = cnt