CODEWITHSUNDEEP
vbaexcel
David G
David G

Reputation: 2347

Function to check if a range is contained in another

I am looking to create a function that checks if a cell (Range1) is contained within Range2. The function would be:

Function IsWithin(Range1 as Range, Range2 as Range) as Boolean

This is meant to go in a Before_DoubleClick event to check that the cell clicked belongs to a range.

Examples of expected input/output (using addresses directly only to make it easier to imagine):

IsWithin("A2", "A1:B3") = True
IsWithin("B1","B1:B2") = True
IsWithin("A3", "A4:C10") = False
IsWithin("A3", "A3") = True

Off the top of my head I can think of a simple way to do this:

Function IsWithin(Range1 as Range, Range2 as Range) as Boolean
    Dim cell2 as range

    For each cell2 in Range2
          If cell2.address = Range1.Address then 
               IsWithin = True
               Exit Function 
          End if 
    Next

End function

Now for the harder part, and the question. If I am selecting a Merged cell that protrudes inside Range2, I'd like it to count as being part of the range (even if some of the merged cell sticks out). What would I need to write to get that done?

Example considering A1:B3 is a merged cell (Still sending addresses instead of range objects as a way to represent it easier):

IsWithin("A1:B3", "A2:D7") = True

Upvotes: 3

Views: 1597

Answers (1)

Bond
Bond

Reputation: 16311

Is there a reason why you're not using Intersect()?

Dim r As Range
Set r = Intersect(Range("A2"), Range("A1:B3"))

If r Is Nothing Then
    Debug.Print "Not in range"
ElseIf r.Address = Range("A2").Address Then
    Debug.Print "Completely within range"
Else
    Debug.Print "Partially within range"
End If

Edit:

As @Bacon mentioned in the comments, this doesn't work with merged cells. But you can use the MergeArea property to test for that. Assuming A1:B1 is a merged range, this should work:

Set r = Intersect(Range("A1").MergeArea, Range("B1:B3"))

If r Is Nothing Then
    Debug.Print "Not in range"
Else
    Debug.Print "Ranges intersect"
End If

MergeArea returns the merged range, if it's part of a merged area. If not, it just returns the single cell. So you should be safe always using MergeArea for the source when you test the intersection, as shown above in the edit.

Upvotes: 3

Related Questions