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

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

--
Tim Zych
SF, CA

"tskogstrom" wrote in message
oups.com...
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

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


On 13 Nov, 10:01, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote:
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

--
Tim Zych
SF, CA

"tskogstrom" wrote in message

oups.com...

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

Yes, I can use this. Thank you!

regards
Tskogstrom