Excel: Selecting Intersection of Two Ranges with a Macro

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

KBCategory: kbother

KBSubcategory:
Additional reference words: 1.00 1.03 1.04 1.06 1.50 2.20 3.00

Microsoft KB Archive/57196

Excel: Selecting Intersection of Two Ranges with a Macro

SUMMARY