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
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
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks