Subtract Ranges

Ornithopter

New Member
Joined
Jul 5, 2004
Messages
32
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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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

'rng3 is your new range

End Sub
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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:

Application.DisplayAlerts = False
'code to subtract ranges
Application.DisplayAlerts = True

HTH
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top