Hi guys,
Just returning to the original query - subtracting one range from another or, as it is sometimes called, establishing the anti-range.
The following functions were code by John Walkenbach and Vasant Nanavati. I added a simple 'Tester' routine to show how they work - this will show the original range, then the one to be 'subtracted' and then the range that is left.
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Function InRange(rngInner As Range, rngOuter As Range) As Boolean
'From John Walkenbach
If rngInner.Parent.Parent.Name = rngOuter.Parent.Parent.Name Then
If rngInner.Parent.Name = rngOuter.Parent.Name Then
If Union(rngInner, rngOuter).Address = rngOuter.Address Then
InRange = True
End If
End If
End If
End Function
Function AntiRange(rngInner As Range, rngOuter As Range) As Range
Dim C As Range
If InRange(rngInner, rngOuter) Then
For Each C In rngOuter.Cells
If Intersect(rngInner, C) Is Nothing Then
If AntiRange Is Nothing Then
Set AntiRange = C
Else
Set AntiRange = Union(C, AntiRange)
End If
End If
Next
End If
'MsgBox AntiRange.Address
End Function
Sub Test()
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim i As Integer
With Sheet1
Set rng1 = .Range("A1:J30")
For i = 1 To 3
rng1.Interior.ColorIndex = 36
Sleep 500
rng1.Interior.ColorIndex = xlColorIndexNone
Sleep 500
Next
.Range("M2").Value = rng1.Address
'the 'outer' range
Set rng2 = .Range("D11:G20")
For i = 1 To 3
rng2.Interior.ColorIndex = 45
Sleep 500
rng2.Interior.ColorIndex = xlColorIndexNone
Sleep 500
Next
.Range("M4").Value = rng2.Address
'the 'inner' range
Set rng3 = AntiRange(rng2, rng1)
.Range("M6").Value = rng3.Address
'the 'outer' less the 'inner'
For i = 1 To 3
rng3.Interior.ColorIndex = 1
Sleep 500
rng3.Interior.ColorIndex = xlColorIndexNone
Sleep 500
Next
End With
Set rng1 = Nothing
Set rng2 = Nothing
Set rng3 = Nothing
End Sub
HTH
I've attached an example workbook containing the above.