Excel: Selecting Intersection of Two Ranges with a Macro

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

   =SELECT(!NAME1 !NAME2)

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