I have two range objects, and I want to subtract range B from range A. That
is, I want the opposite of Application.Intersect. This match concept is
illustrated at http://www.mathwords.com/s/set_subtraction.htm

I don't believe there's a single command for this, but can anyone provide
some thoughts? I plan on approaching this using a loop,
Application.Intersect, and Union() commands.

Thanks,
Matthew Pfluger

If Union joins them, how about a divorce??:

Sub divorce()
Set r1 = Range("A1:D10")
Set r2 = Range("B5:F5")
Set rExtraction = Nothing
For Each r In r1
If Intersect(r, r2) Is Nothing Then
If rExtraction Is Nothing Then
Set rExtraction = r
Else
Set rExtraction = Union(rExtraction, r)
End If
End If
Next
MsgBox (rExtraction.Address)
rExtraction.Select
End Sub

Basically rExtraction is r1 with r2 removed.
--
Gary''s Student - gsnu200784


"Matthew Pfluger" wrote:

I have two range objects, and I want to subtract range B from range A. That
is, I want the opposite of Application.Intersect. This match concept is
illustrated at http://www.mathwords.com/s/set_subtraction.htm

I don't believe there's a single command for this, but can anyone provide
some thoughts? I plan on approaching this using a loop,
Application.Intersect, and Union() commands.

Thanks,
Matthew Pfluger

The 'Tom Ogilvy developed further by Dana DeLouis' approach to subtracting
ranges, and similar to get an inverse range, goes something like this

Sub test()
Dim r1 As Range, r2 As Range, r3 As Range

Set r1 = Range("c5:e9")
Set r2 = Range("e2:f12")

r2.Validation.Add xlValidateInputOnly
r1.Validation.Delete
On Error Resume Next
Set r3 = r2.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
r2.Validation.Delete
If Not r3 Is Nothing Then
r3.Select
End If

End Sub

Not included above but required is a check to ensure there is no existing
validation, if there is there are alternative similar methods.

If the purpose is to do a maths calculation (ie subtraction) I'm not sure
producing a subtracted range is an efficient way of going about it. At least
I wouldn't have though so with two single area intersecting ranges as in the
simplified example. Perhaps it depends on what you mean by subtraction.

Regards,
Peter T



"Matthew Pfluger" wrote in
message ...
I have two range objects, and I want to subtract range B from range A.
That
is, I want the opposite of Application.Intersect. This match concept is
illustrated at http://www.mathwords.com/s/set_subtraction.htm

I don't believe there's a single command for this, but can anyone provide
some thoughts? I plan on approaching this using a loop,
Application.Intersect, and Union() commands.

Thanks,
Matthew Pfluger

Thanks, Gary! Here's a modification of the code that changes it to a
function and includes some input checking and a test routine:

Function Subtract(ByRef rngToSubtractFrom As Range, _
ByRef rngToSubtract As Range) As Range
Dim rngSubtraction As Range ' result of
subtracting rngToSubtract from rngToSubtractFrom
Set rngSubtraction = Nothing

If rngToSubtractFrom Is Nothing Then
' Can't subtract anything from a null value

ElseIf rngToSubtract Is Nothing Then
' Similar to subtracting anything by zero
Set rngSubtraction = rngToSubtractFrom

Else
' Both inputs are not Nothing

Dim rngCell As Range
For Each rngCell In rngToSubtractFrom
If Intersect(rngCell, rngToSubtract) Is Nothing Then
If rngSubtraction Is Nothing Then
Set rngSubtraction = rngCell
Else
Set rngSubtraction = Union(rngSubtraction, rngCell)
End If
End If
Next
End If

If Not rngSubtraction Is Nothing Then
Set Subtract = rngSubtraction
Else
Set Subtract = Nothing
End If

End Function

Sub TestSubtract()
Dim rng As Range
Set rng = Subtract(Union(Range("A1:D10"), Range("a12:D13")), _
Union(Range("B5:F5"), Range("H5")))

With rng
.Select
.Interior.ColorIndex = 7
End With

Set rng = Subtract(Range("H5"), Nothing)

With rng
.Select
.Interior.ColorIndex = 25
End With

End Sub

What do you think of the Tom Ogilvy's method (posted below by Peter T)? It
is obviously much faster, though I don't think it's as robust. Is the
validation problem really a big deal?

Matthew Pfluger

"Gary''s Student" wrote:

If Union joins them, how about a divorce??:

Sub divorce()
Set r1 = Range("A1:D10")
Set r2 = Range("B5:F5")
Set rExtraction = Nothing
For Each r In r1
If Intersect(r, r2) Is Nothing Then
If rExtraction Is Nothing Then
Set rExtraction = r
Else
Set rExtraction = Union(rExtraction, r)
End If
End If
Next
MsgBox (rExtraction.Address)
rExtraction.Select
End Sub

Basically rExtraction is r1 with r2 removed.
--
Gary''s Student - gsnu200784


"Matthew Pfluger" wrote:

I have two range objects, and I want to subtract range B from range A. That
is, I want the opposite of Application.Intersect. This match concept is
illustrated at http://www.mathwords.com/s/set_subtraction.htm

I don't believe there's a single command for this, but can anyone provide
some thoughts? I plan on approaching this using a loop,
Application.Intersect, and Union() commands.

Thanks,
Matthew Pfluger

By "Math Operation", I meant only the theoretical sense. That is, the two
ranges represent sets, and subtract can be a set operation.

How would you handle cases where either range had validation? I know you
can create a validation object, but can you just set a range's validation to
that object? And what there were multiple validations in each range?

I like the speed and simplicity, but the error checking is daunting. I
welcome your input. Thanks,
Matthew Pfluger

"Peter T" wrote:

The 'Tom Ogilvy developed further by Dana DeLouis' approach to subtracting
ranges, and similar to get an inverse range, goes something like this

Sub test()
Dim r1 As Range, r2 As Range, r3 As Range

Set r1 = Range("c5:e9")
Set r2 = Range("e2:f12")

r2.Validation.Add xlValidateInputOnly
r1.Validation.Delete
On Error Resume Next
Set r3 = r2.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
r2.Validation.Delete
If Not r3 Is Nothing Then
r3.Select
End If

End Sub

Not included above but required is a check to ensure there is no existing
validation, if there is there are alternative similar methods.

If the purpose is to do a maths calculation (ie subtraction) I'm not sure
producing a subtracted range is an efficient way of going about it. At least
I wouldn't have though so with two single area intersecting ranges as in the
simplified example. Perhaps it depends on what you mean by subtraction.

Regards,
Peter T



"Matthew Pfluger" wrote in
message ...
I have two range objects, and I want to subtract range B from range A.
That
is, I want the opposite of Application.Intersect. This match concept is
illustrated at http://www.mathwords.com/s/set_subtraction.htm

I don't believe there's a single command for this, but can anyone provide
some thoughts? I plan on approaching this using a loop,
Application.Intersect, and Union() commands.

Thanks,
Matthew Pfluger

How would you handle cases where either range had validation?

Check if the whole sheet or merely if either of the two ranges include any
DV with Specialcells (similar to the example).

but can you just set a range's validation to
that object?

By Object I assume you mean to one of the range's. Yes of course and that's
exactly how it works.

Apply DV to whole the range you want to subtract from, say r2
Remove all DV in the other range r1 inclucing any that overlap into r2
Use special cells to return all (remaining) DV cells within the original r2

And what there were multiple validations in each range?

Try a very similar approach with (dummy) CF It's pretty rare for a sheet to
contain both DV & CF in your ranges of interest. But if so need to use other
methods.

And what there were multiple validations in each range?

Why not try it and see for yourself. OK, yes it does though the method is
not efficent if either range has several hundred areas.

Regards,
Peter T


"Matthew Pfluger" wrote in
message ...
By "Math Operation", I meant only the theoretical sense. That is, the two
ranges represent sets, and subtract can be a set operation.

How would you handle cases where either range had validation? I know you
can create a validation object, And what there were multiple validations
in each range?

I like the speed and simplicity, but the error checking is daunting. I
welcome your input. Thanks,
Matthew Pfluger

"Peter T" wrote:

The 'Tom Ogilvy developed further by Dana DeLouis' approach to
subtracting
ranges, and similar to get an inverse range, goes something like this

Sub test()
Dim r1 As Range, r2 As Range, r3 As Range

Set r1 = Range("c5:e9")
Set r2 = Range("e2:f12")

r2.Validation.Add xlValidateInputOnly
r1.Validation.Delete
On Error Resume Next
Set r3 = r2.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
r2.Validation.Delete
If Not r3 Is Nothing Then
r3.Select
End If

End Sub

Not included above but required is a check to ensure there is no
existing
validation, if there is there are alternative similar methods.

If the purpose is to do a maths calculation (ie subtraction) I'm not
sure
producing a subtracted range is an efficient way of going about it. At
least
I wouldn't have though so with two single area intersecting ranges as in
the
simplified example. Perhaps it depends on what you mean by subtraction.

Regards,
Peter T



"Matthew Pfluger" wrote in
message ...
I have two range objects, and I want to subtract range B from range A.
That
is, I want the opposite of Application.Intersect. This match concept
is
illustrated at http://www.mathwords.com/s/set_subtraction.htm

I don't believe there's a single command for this, but can anyone
provide
some thoughts? I plan on approaching this using a loop,
Application.Intersect, and Union() commands.

Thanks,
Matthew Pfluger

Being robust is important, especially for real-world applications.
Frequently when I respond to posts, I will give quick "demo"-type coding. I
may not include necessary OnError logic if it does not contribute to the
demonstration of technique.

Error anticipation/traping IS important and should always be carefully
thought out.

(after all, the code is executed by humans)
--
Gary''s Student - gsnu200784


"Matthew Pfluger" wrote:

Thanks, Gary! Here's a modification of the code that changes it to a
function and includes some input checking and a test routine:

Function Subtract(ByRef rngToSubtractFrom As Range, _
ByRef rngToSubtract As Range) As Range
Dim rngSubtraction As Range ' result of
subtracting rngToSubtract from rngToSubtractFrom
Set rngSubtraction = Nothing

If rngToSubtractFrom Is Nothing Then
' Can't subtract anything from a null value

ElseIf rngToSubtract Is Nothing Then
' Similar to subtracting anything by zero
Set rngSubtraction = rngToSubtractFrom

Else
' Both inputs are not Nothing

Dim rngCell As Range
For Each rngCell In rngToSubtractFrom
If Intersect(rngCell, rngToSubtract) Is Nothing Then
If rngSubtraction Is Nothing Then
Set rngSubtraction = rngCell
Else
Set rngSubtraction = Union(rngSubtraction, rngCell)
End If
End If
Next
End If

If Not rngSubtraction Is Nothing Then
Set Subtract = rngSubtraction
Else
Set Subtract = Nothing
End If

End Function

Sub TestSubtract()
Dim rng As Range
Set rng = Subtract(Union(Range("A1:D10"), Range("a12:D13")), _
Union(Range("B5:F5"), Range("H5")))

With rng
.Select
.Interior.ColorIndex = 7
End With

Set rng = Subtract(Range("H5"), Nothing)

With rng
.Select
.Interior.ColorIndex = 25
End With

End Sub

What do you think of the Tom Ogilvy's method (posted below by Peter T)? It
is obviously much faster, though I don't think it's as robust. Is the
validation problem really a big deal?

Matthew Pfluger

"Gary''s Student" wrote:

If Union joins them, how about a divorce??:

Sub divorce()
Set r1 = Range("A1:D10")
Set r2 = Range("B5:F5")
Set rExtraction = Nothing
For Each r In r1
If Intersect(r, r2) Is Nothing Then
If rExtraction Is Nothing Then
Set rExtraction = r
Else
Set rExtraction = Union(rExtraction, r)
End If
End If
Next
MsgBox (rExtraction.Address)
rExtraction.Select
End Sub

Basically rExtraction is r1 with r2 removed.
--
Gary''s Student - gsnu200784


"Matthew Pfluger" wrote:

I have two range objects, and I want to subtract range B from range A. That
is, I want the opposite of Application.Intersect. This match concept is
illustrated at http://www.mathwords.com/s/set_subtraction.htm

I don't believe there's a single command for this, but can anyone provide
some thoughts? I plan on approaching this using a loop,
Application.Intersect, and Union() commands.

Thanks,
Matthew Pfluger

I have two range objects, and I want to subtract range B from range A.

As a side note, in Mathematics, this is called the "Complement."

http://en.wikipedia.org/wiki/Complem...8set_theory%29

For example, if you have objects (or cells, or whatever) 1,2,3 & 4, and
remove 1 & 3, you are left with 2 & 4.

Complement[{1, 2, 3, 4}, {1, 3}]
{2, 4}

Using the Dictionary Object in Excel vba is also another way to handle
certain functions like this.
--
HTH :)
Dana DeLouis


"Matthew Pfluger" wrote in
message ...
Thanks, Gary! Here's a modification of the code that changes it to a
function and includes some input checking and a test routine:

Function Subtract(ByRef rngToSubtractFrom As Range, _
ByRef rngToSubtract As Range) As Range
Dim rngSubtraction As Range ' result of
subtracting rngToSubtract from rngToSubtractFrom
Set rngSubtraction = Nothing

If rngToSubtractFrom Is Nothing Then
' Can't subtract anything from a null value

ElseIf rngToSubtract Is Nothing Then
' Similar to subtracting anything by zero
Set rngSubtraction = rngToSubtractFrom

Else
' Both inputs are not Nothing

Dim rngCell As Range
For Each rngCell In rngToSubtractFrom
If Intersect(rngCell, rngToSubtract) Is Nothing Then
If rngSubtraction Is Nothing Then
Set rngSubtraction = rngCell
Else
Set rngSubtraction = Union(rngSubtraction, rngCell)
End If
End If
Next
End If

If Not rngSubtraction Is Nothing Then
Set Subtract = rngSubtraction
Else
Set Subtract = Nothing
End If

End Function

Sub TestSubtract()
Dim rng As Range
Set rng = Subtract(Union(Range("A1:D10"), Range("a12:D13")), _
Union(Range("B5:F5"), Range("H5")))

With rng
.Select
.Interior.ColorIndex = 7
End With

Set rng = Subtract(Range("H5"), Nothing)

With rng
.Select
.Interior.ColorIndex = 25
End With

End Sub

What do you think of the Tom Ogilvy's method (posted below by Peter T)?
It
is obviously much faster, though I don't think it's as robust. Is the
validation problem really a big deal?

Matthew Pfluger

"Gary''s Student" wrote:

If Union joins them, how about a divorce??:

Sub divorce()
Set r1 = Range("A1:D10")
Set r2 = Range("B5:F5")
Set rExtraction = Nothing
For Each r In r1
If Intersect(r, r2) Is Nothing Then
If rExtraction Is Nothing Then
Set rExtraction = r
Else
Set rExtraction = Union(rExtraction, r)
End If
End If
Next
MsgBox (rExtraction.Address)
rExtraction.Select
End Sub

Basically rExtraction is r1 with r2 removed.
--
Gary''s Student - gsnu200784


"Matthew Pfluger" wrote:

I have two range objects, and I want to subtract range B from range A.
That
is, I want the opposite of Application.Intersect. This match concept
is
illustrated at http://www.mathwords.com/s/set_subtraction.htm

I don't believe there's a single command for this, but can anyone
provide
some thoughts? I plan on approaching this using a loop,
Application.Intersect, and Union() commands.

Thanks,
Matthew Pfluger