I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Rless

You can easily combine two ranges with Union, you can get their common parts with Intersect - but if you need to get all cells which are in Range1 but not in Range2:

Function rless(r1 As Range, r2 As Range) As Range
'Returns all cells in r1 which are not in r2.
'Can return a multi-range.
'Reverse("moc.liborplus.www") V0.20 PB 04-May-2010
Dim v As Variant, r As Range, r3 As Range
Dim bFirst As Boolean
Set r3 = Application.Intersect(r1, r2)
If r3 Is Nothing Then
    Set rless = r1
    Exit Function
End If
bFirst = True
For Each v In r1
    If Application.Intersect(r3, v) Is Nothing Then
        If bFirst Then
            Set r = v
            bFirst = False
        Else
            Set r = Application.Union(r, v)
        End If
    End If
Next v
Set rless = r
End Function

Sub test()
Dim v As Variant
Worksheets("Sheet1").Activate
For Each v In rless(Range("A1:D4"), Range("B2:C3"))
    Debug.Print v.Address
Next v
End Sub

Sub test2()
Dim v As Variant
Worksheets("Sheet1").Activate
For Each v In rless(rless(Range("A1:D4"), Range("B2:C3")), Range("A1:B2"))
    Debug.Print v.Address
Next v
End Sub

Sulprobil   Get it done   Contact   Disclaimer   Download