Union and Intersect Methods

Union and Intersect are methods of the Application object, but they can be used without preceding them with a reference to Application because they are members of <globals>. They can be very useful tools, as you shall see.

Use Union when you want to generate a range from two or more blocks of cells. Use Intersect when you want to find the cells that are common to two or more ranges, or in other words, where the ranges overlap. The following event procedure, entered in the module behind a worksheet, illustrates how you can apply the two methods to prevent a user from selecting cells in two ranges B10:F20 and H10:L20. One use for this routine is to prevent a user from changing data in these two blocks:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngForbidden As Range

'Define forbidden range

Set rngForbidden = Union(Range("B10:F20"), Range("H10:L20"))

'If selection does not overlap forbidden areas, do nothing If Intersect(Target, rngForbidden) Is Nothing Then Exit Sub

'Select A1 and issue warning Range("A1").Select

MsgBox "You can't select cells in " & rngForbidden.Address, vbCritical End Sub

If you are not familiar with event procedures, refer to the "Events" section in Chapter 1. For more information on event procedures, see Chapter 10.

The Worksheet_SelectionChange event procedure is triggered every time the user selects a new range in the worksheet associated with the module containing the event procedure. The preceding code uses the Union method to define a forbidden range consisting of the two non-contiguous ranges. It then uses the Intersect method, in the If test, to see if the Target range, which is the new user selection, is within the forbidden range. Intersect returns Nothing if there is no overlap and the Sub exits. If there is an overlap, the code in the two lines following the If test are executed — cell A1 is selected and a warning message is issued to the user.

0 0

Post a comment