Microsoft KB Archive/57196

{| = Excel: Selecting Intersection of Two Ranges with a Macro =
 * width="100%"|

Last reviewed: July 24, 1995

Article ID: Q57196

SUMMARY
In Microsoft Excel, you can indicate an intersection of two ranges by separating the ranges with a single space. For example:

The Reference              Returns the Range -              -

$A$1:$B$10 $B$3:$D$12      $B$3:$B$10 $A:$C $1:!10               $A$1:$C$10 Name1 Name2                intersection of two named ranges

As an example, the following SELECT statement can be used in a macro to select the intersection of two named ranges =SELECT(!NAME1 !NAME2) where NAME1 and NAME2 are defined names referring to ranges on a worksheet or macro sheet. The blank space between the defined names instructs Excel to find the intersection of the two ranges.

Note: If the ranges never intersect (that is, they're parallel), the reference will return #NULL! and the SELECT statement will cause a macro error, halting the macro.

For information about how to do this in Microsoft Excel 5.0, please see the following article(s) in the Microsoft Knowledge Base:

ARTICLE-ID: Q120198 TITLE    : XL5: How to Select Cells/Ranges Using Visual Basic Procedures
 * }