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).