Option Explicit
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
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
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"
For Each Cell In BigRange
If Cell Like "*x*" Then MsgBox "An ''x'' was found at " & Cell.Address
Next Cell
MsgBox "(The union values will now be changed)"
Range("H1:H19").ClearContents
BigRange = "HELLO"
MsgBox "The union values have all been changed"
MsgBox "The union values will now be cleared"
BigRange.ClearContents
End Sub
Sub MultiSelect()
Dim Cell As Range
[A1:A19, A1:G3, A17:G19, C3:C9, C11:C17, E3:E17, G1:G19, I2:I11, I10:M11, I13:M19, M2:M11].Select
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"
For Each Cell In Selection
If Cell Like "*x*" Then MsgBox "An ''x'' was found at " & Cell.Address
Next Cell
MsgBox "(The selection values will now be changed)"
[H1:H19].ClearContents
Selection = "HELLO"
MsgBox "The selection values have all been changed"
MsgBox "The selection values will now be cleared"
Selection.ClearContents
End Sub
Sub SelectIntersects()
Dim SmallRange As Range
Set SmallRange = Intersect([A1:A19], [A1:K3], [A2:F10])
Union([A1:A19], [A1:K3], [A2:F10]).Select
MsgBox "This is the Union of [A1:A19], [A1:K3], and [A2:F10]", , _
"UNION Example..."
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 / coincide)", , "INTERSECT Example..."
SmallRange.ClearContents
End If
End Sub
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
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)
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
