VBA/Excel/Access/Word/Excel/Range Union

Determining whether a range is contained in another range

 
Function InRange(rng1, rng2) As Boolean
    InRange = False
    If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
        If rng1.Parent.Name = rng2.Parent.Name Then
            If Union(rng1, rng2).Address = rng2.Address Then
                InRange = True
            End If
        End If
    End If
End Function

Returns True if rng1 is a subset of rng2

 
Function InRange(rng1, rng2) As Boolean
    InRange = False
    If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
        If rng1.Parent.Name = rng2.Parent.Name Then
            If Union(rng1, rng2).Address = rng2.Address Then
                InRange = True
            End If
        End If
    End If
End Function

Use Union when you want to generate a range from two or more blocks of cells.

 
     Private Sub Worksheet_SelectionChange(ByVal Target As Range)
         Dim rngForbidden As Range
         Set rngForbidden = Union(Range("B10:F20"), Range("H10:L20"))
         Range("A1").Select
         MsgBox "You can"t select cells in " & rngForbidden.Address, vbCritical
     End Sub

Using the Union Method to Join Multiple Ranges

 
Sub unionDemo()
    Set UnionRange = union(range("Range1"), range("Range2"))
    With UnionRange
        .Formula = "=RAND()"
        .font.bold = True
    End With
End Sub