# Subtract Ranges

This is a discussion on Subtract Ranges within the Excel Questions forums, part of the Question Forums category; Is there a quick way to subtract ranges? Example: RangeA = A1:A20 RangeB = A1:A10 RangeC = subtract(RangeA, RangeB) Now ...

1. ## Subtract Ranges

Is there a quick way to subtract ranges?

Example:
RangeA = A1:A20
RangeB = A1:A10

RangeC = subtract(RangeA, RangeB)

Now RangeC = A10:A20

Is there something like that?

2. ## Re: Subtract Ranges

Here is something I cam up with, its not completely simplistic as you hoped, but its also not overly complex. HTH
Code:
```Sub subract_ranges()
Dim c As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range

Set rng1 = Range("A1:A20")
Set rng2 = Range("A1:A10")

Set rng3 = Nothing

For Each c In rng1
If Intersect(c, rng2) Is Nothing Then
If rng3 Is Nothing Then
Set rng3 = c
Else
Set rng3 = Union(rng3, c)
End If
End If
Next c

End Sub```

3. ## Re: Subtract Ranges

Kewl, thanx!

Never thought about it that way...
Kinda nifty actually

4. ## Re: Subtract Ranges

Although Martinee had a kewl solution,
I have discovered a one-liner that achieves my purpose.

rangeA.RowDifferences(RangeB)

Still a neat solution martinee ;D

5. ## Re: Subtract Ranges

Okay, the RowDifferences doesn't work the way I
thought.

Is ther any way to deselect a Range or remove
a range from another range (without removing the
underlying data, just the range definition).

I tried the following code:
Code:
```'Does a set difference on two ranges.
'For example:
'   subtractRanges = subtractee - subtractor
'
'Returns the range of cells that are IN the
'subtractee and NOT in the subtractor.
'
'Example 2:
'RangeA = "A1:A20"
'RangeB = "A1:A10"
'RangeC = subtractRanges(RangeA, RangeB)
'
'RangeC contains "A11:A20"
Function subractRanges(subtractee As Range, subtractor As Range) As Range

Dim c As Range

For Each c In subtractee
If Intersect(c, subtractor) Is Nothing Then
If subractRanges Is Nothing Then
Set subractRanges = c
Else
Set subractRanges = Union(subractRanges, c)
End If
End If
Next c
End Function```
However the opteration I want to do is this:
Dim garbage As Range
Set garbage = SubtractRanges.subractRanges(ActiveSheet.Cells, Range(ActiveSheet.PageSetup.PrintArea))

If you do this, the subroutine runs for A LONG TIME as it is trying
to compare every cell in the entire sheet to the print area.

Suggestions?

6. ## Re: Subtract Ranges

I'm not sure what you would like. If you need to compare the entire sheet to the print area, that seems to be the way to do it, but if you want to maybe speed things up, try:

'code to subtract ranges

HTH

7. ## Re: Subtract Ranges

Okay, so I've narrowed down the comparison
to just:

Dim garbage As Range
Set garbage = SubtractRanges.subractRanges(ActiveSheet.UsedRange, Range(ActiveSheet.PageSetup.PrintArea))

However, excel does not re-calculate the UsedRange properly
after I delete the garbage area... the Used range still includes
more than the print area even though there is nothing outside
the printarea after deleting the garbage.

Thoughts?

8. ## Re: Subtract Ranges

Saving your workbook will update the used range property.

HTH

9. ## Re: Subtract Ranges

That is pretty silly,
but it will do.

Thanks again...

10. ## Re: Subtract Ranges

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•