Excel

Working with Union and Intersect

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

Description:

After running the code different parts of the worksheet will be simultaneously selected.

Discussion:

Union is a method for working with multiple ranges simultaneously. It is similar to the 'multi-select' method given here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=352. Because the ranges in Union must be all specified AS ranges it is much "wordier" than a simple multi-select/multi-range method. but as it is more frequently used, you should become familiar with the Union method. Intersect is a completely different kettle of fish, in that only the small range where two or more larger ranges "intersect" is selected or otherwise worked with (i.e. the Intersect is where ALL these ranges coincide with each other). The use of Union and Intersect is often a mystery to many, so here are some simple examples (and one that's not so simple) that may help you to become more familiar with their use.

Code:

```			Option Explicit

'Union c.f. 'multi-range'; Union ranges must be specified AS ranges.
'For instance:
'Range("A1:G1, A1:A20, G1:G20") is completely identical to
'Union(Range("A1:G1"), Range("A1:A20"), Range("G1:G20"))
'
'Fortunately, these can be simplified by using shorthand notation to:
'[A1:G1, A1:A20, G1:G20] = Union([A1:G1], [A1:A20], [G1:G20])
'(where, in shorthand notation, [A1:G1] = Range("A1:G1")...etc.)

Sub BasicUnionExample()
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range

Set Rng1 = Union([A1:A19], [K3:K5])
Set Rng2 = Union([A1:A19], [F2:F7])
Set Rng3 = Union([A1:K3], [F10:G5])

Union(Rng1, Rng2).Interior.ColorIndex = 3
MsgBox "This is the Union(Rng1, Rng2)  i.e. [A1:A19], [K3:K5], [A1:A19], and [F2:F7]"
Union(Rng1, Rng2).Interior.ColorIndex = xlNone

Union(Rng1, Rng3).Interior.ColorIndex = 4
MsgBox "This is the Union(Rng1, Rng3)  i.e. [A1:A19], [K3:K5], [A1:K3], and [F10:G5]"
Union(Rng1, Rng3).Interior.ColorIndex = xlNone

Union(Rng2, Rng3).Interior.ColorIndex = 5
MsgBox "This is the Union(Rng2, Rng3)  i.e. [A1:A19], [F2:F7], [A1:K3], and [F10:G5]"
Union(Rng2, Rng3).Interior.ColorIndex = xlNone

Union(Rng1, Rng2, Rng3).Interior.ColorIndex = 7
MsgBox "This is the Union(Rng1, Rng2, Rng3)"
Union(Rng1, Rng2, Rng3).Interior.ColorIndex = xlNone
End Sub

Sub SelectUnion()

Dim Cell As Range, BigRange As Range

'select all the ranges given in BigRange
Set BigRange = Union([A1:A19], [A1:G3], [A17:G19], [C3:C9], [C11:C17], [E3:E17], _
[g1:g19], [I2:I11], [I10:M11], [I13:M19], [M2:M11])
BigRange.Select

'We'll add something & do a search within this union
MsgBox "This is the union, we'll now add some values" & vbLf & _
"(x) and search this union for these values"
Range("F18, M18, H1:H19") = "x"
'Now search the union values only
For Each Cell In BigRange
If Cell Like "*x*" Then MsgBox "An ''x'' was found at " & Cell.Address
Next Cell

'do something with the union
MsgBox "(The union values will now be changed)"
Range("H1:H19").ClearContents
BigRange = "HELLO"
MsgBox "The union values have all been changed"

'Clear the union values
MsgBox "The union values will now be cleared"
BigRange.ClearContents
End Sub

'(http://www.vbaexpress.com/kb/getarticle.php?kb_id=352)
'I'll enclose a shorthand notation version of its' code below
'so you can run a complete back-to back comparison of these
'two different methods

Sub MultiSelect()

Dim Cell As Range

'Multi-selection - separate the selection ranges with commas
[A1:A19, A1:G3, A17:G19, C3:C9, C11:C17, E3:E17, G1:G19, I2:I11, I10:M11, I13:M19, M2:M11].Select

'We'll add something & do a search within the selection
MsgBox "This is the selection, we'll now add some values" & vbLf & _
"(x) and search the selection for these values"
[F18, M18, H1:H19] = "x"
'Now search the selected values only
For Each Cell In Selection
If Cell Like "*x*" Then MsgBox "An ''x'' was found at " & Cell.Address
Next Cell

'do something with the selection
MsgBox "(The selection values will now be changed)"
[H1:H19].ClearContents
Selection = "HELLO"
MsgBox "The selection values have all been changed"

'Clear the selection
MsgBox "The selection values will now be cleared"
Selection.ClearContents
End Sub

'Intersect is the smaller range where two or more larger ranges
'"intersect" each other (i.e. where they ALL coincide with each
'other). If there is any one of the named given range that does
'NOT intersect any of the others you will need an error-handling
'procedure to cope with this eventuality.
'(example given is {If SmallRange Is Nothing Then...}=Error)

Sub SelectIntersects()
Dim SmallRange As Range
Set SmallRange = Intersect([A1:A19], [A1:K3], [A2:F10])

'for comparative demonstration, select the Union first
Union([A1:A19], [A1:K3], [A2:F10]).Select
MsgBox "This is the Union of [A1:A19], [A1:K3], and [A2:F10]", , _
"UNION Example..."

'now select the Intersect (the range where two or more ranges co-exist)
If SmallRange Is Nothing Then
MsgBox "Cannot process, the given ranges do not intersect", , _
"ERROR (Out of range)..."
Exit Sub
Else
SmallRange.Select
SmallRange = "XXXX"
MsgBox "The selected range " & SmallRange.Address & _
" is the Intersect of [A1:A19], [A1:K3] and [A2:F10]" & vbLf & _
"(i.e. this range is the only point(s) where all these " & _
"three ranges meet / co-incide)", , "INTERSECT Example..."
SmallRange.ClearContents
End If
End Sub

'This is an example that uses both Union and Intersect

Sub SearchAreas()
Dim Bookmark As String, FirstRange As Range
Dim RangeSelected As Range, Cell As Range
Dim MyResponse As VbMsgBoxResult
Sheet2.Activate
MsgBox "Here is an example that uses both Union and" & vbLf & _
"Intersect to search the sheet by regions...", , _
"Example Using both Union & Intersect"
With ActiveSheet.Cells
Set Cell = .Find(What:="*", LookIn:=xlValues)
If Cell Is Nothing Then
MsgBox "The worksheet is empty", vbInformation, _
"Blank Worksheet"
Exit Sub
End If
Cell.CurrentRegion.Select
MyResponse = MsgBox("Is this the current region?", _
vbYesNo, "Current Region")
If MyResponse = vbYes Then
Set RangeSelected = Selection
GoTo Finish:
End If
Set FirstRange = Selection
Do
If Intersect(FirstRange, Selection) Is Nothing Then '< Intersect
MyResponse = MsgBox("Is this the current region?", _
vbYesNo, "Current Region")
If MyResponse = vbYes Then
Set RangeSelected = Selection
GoTo Finish:
End If
End If
If FirstRange Is Nothing Then
Set FirstRange = Selection
Else
Set FirstRange = Union(FirstRange, Selection) '< Union
End If
Set Cell = .FindNext(Cell)
Cell.CurrentRegion.Select
Loop While Not Cell Is Nothing And Cell.Address <> Bookmark
End With
Finish:
If MyResponse = vbYes Then
MsgBox "You selected range " & RangeSelected.Address, _
, "Range Selected"
Else
MsgBox "You did not select a range", , "No Range Selected"
End If

Sheet1.Activate
End Sub

'// NOTE: The examples given above all use "Select" merely to illustrate
'// the principles involved. You should note that it is not always essential
'// to 'Select', as you can very often work with values in Union, Intersect,
'// or multi-ranges by just "referring" to them.
'// (Selection is frequently over-used, and it slows down operations
'// considerably. In most cases, it's only necessary to use Select when
'// you want the user to "read", "view", or "input" data).

```

How to use:

1. Open an Excel workbook
2. Select Tools/Macro/Visual Basic Editor
3. In the VBE window, select Insert/Module
4. Copy and paste the code into the Module
6. Select Tools/Macro/Macros.../(any one of the Macros)/Run

Test the code:

1. Select Tools/Macro/Macros.../
2. Now select any one of the macros
3. Click Run