Find range differens between two ranges/areas?

[tskogstrom]

I got two ranges/areas like:

rng1 = A1, A2:A4, A10:A100, A102
rng2 = A2:A3,A15;A102:A200

I need to get the ranges who differ:

Diff1 - the range content in rng1 "minus" rng2
A1, A4, A10:A14, A16:A100

Diff2 - the range content in rng2 "minus" rng1
A103:A200

- rng1, rng2, Diff1, Diff2 could also all be "nothing"
- the ranges come from a set rng1 =
Columns("A:A").SpecialCells(xlCellTypeFormulas, xlNumbers/xlLogical)
where the cell's contents are a formula with either a boolean "false"
or value 1 as result.


I have tried with intersect method, i have made for...next loops etc.
but can't make it work.

Can anybody give me a solution of this? I'll be forever grateful!

kind regards
Tskogstrom

 

[Tim Zych]

If one rng is Logical, the other Numbers, isn't that sufficient to create
non-intersecting ranges? The SpecialCells mention is not very clear. The
solution may be in that approach.

On the other hand if that does fail, here's a workaround:

Sub RngExtract()

Dim rng1 As Range, rng2 As Range
Dim cell1 As Range, cell2 As Range
Dim diff1 As Range, diff2 As Range
Set rng1 = Range("A1, A2:A4, A10:A100, A102")
Set rng2 = Range("A2:A4,A15,A102:A200")

' First pass, rng1 minus rng2
For Each cell1 In rng1.Cells
If Intersect(cell1, rng2) Is Nothing Then
If diff1 Is Nothing Then
Set diff1 = cell1
Else
Set diff1 = Union(diff1, cell1)
End If
End If
Next

If diff1 Is Nothing Then
Debug.Print "rng1 was a total subset of rng2."
Else
Debug.Print "rng1 without rng2 = " & """" & diff1.Address(0, 0) &
""""
End If

' Second pass, rng2 minus rng1
For Each cell2 In rng2.Cells
If Intersect(cell2, rng1) Is Nothing Then
If diff2 Is Nothing Then
Set diff2 = cell2
Else
Set diff2 = Union(diff2, cell2)
End If
End If
Next

If diff2 Is Nothing Then
Debug.Print "rng2 was a total subset of rng1."
Else
Debug.Print "rng2 without rng1 = " & """" & diff2.Address(0, 0) &
""""
End If

End Sub

 

[tskogstrom]

Great, it looks like what i've trying to create without success. I'll
try it right now and see. I'll be back to tell how it went.

regards
Tskogstrom

 

[tskogstrom]

Yes, I can use this. Thank you!

regards
Tskogstrom