Determining if a Range is Contained in a Range
In some situations, you may need to determine if a particular range is contained within another range. For example, you may need to determine if the active cell is in a particular range.
The InRange function, listed below, accepts two arguments (both Range objects). The function returns True if the first range is contained in the second range. Notice that the function checks to make sure that the two range arguments are contained in the same sheet and in the same workbook.
You can use the InRange function in your VBA code, or in a worksheet function.
The InRange Function
The VBA code for the InRange function is listed below.
Function InRange(rng1, rng2) As Boolean
' Returns True if rng1 is a subset of rng2
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
An Example
Listed below is a simple example that uses the InRange function. The subroutine prompts the user to select a range, and then checks the range using the InRange function. If the user's selection is not within A1:E20, the prompt appears again.
Sub Test()
Dim ValidRange As Range, UserRange As Range
Dim SelectionOK As Boolean
Set ValidRange = Range("A1:E20")
SelectionOK = False
On Error Resume Next
Do Until SelectionOK = True
Set UserRange = Application.InputBox(Prompt:="Select a range", Type:=8)
If TypeName(UserRange) = "Empty" Then Exit Sub
If InRange(UserRange, ValidRange) Then
MsgBox "The range is valid."
SelectionOK = True
Else
MsgBox "Select a range within " & ValidRange.Address
End If
Loop
End Sub