Excel

Working with Union and Intersect

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

johnske

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:

instructions for use

			

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 'If you haven't read the thread "Working with multi-selections" '(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 Bookmark = Cell.Address 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
  5. Now select File/Close and Return To Microsoft Excel
  6. Select Tools/Macro/Macros.../(any one of the Macros)/Run
  7. Now follow the prompts
 

Test the code:

  1. Select Tools/Macro/Macros.../
  2. Now select any one of the macros
  3. Click Run
  4. Alternatively, download the attachment 'Union&Intersect.zip'
  5. Extract the enclosed workbook, open it and click any one of the buttons to view the examples.
 

Sample File:

Union&Intersect.zip 22.02KB 

Approved by mdmackillop


This entry has been viewed 416 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express