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

Determining whether a range is contained in another range

   <source lang="vb">

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

</source>
   
  

Returns True if rng1 is a subset of rng2

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

    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
</source>
   
  

Using the Union Method to Join Multiple Ranges

   <source lang="vb">

Sub unionDemo()

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

End Sub

</source>