Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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. #1
    New Member
    Join Date
    Jul 2004
    Posts
    32

    Default 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. #2
    Board Regular
    Join Date
    Nov 2003
    Posts
    960

    Default 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
    
    'rng3 is your new range
    
    End Sub

  3. #3
    New Member
    Join Date
    Jul 2004
    Posts
    32

    Default Re: Subtract Ranges

    Kewl, thanx!

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

  4. #4
    New Member
    Join Date
    Jul 2004
    Posts
    32

    Default 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. #5
    New Member
    Join Date
    Jul 2004
    Posts
    32

    Default 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. #6
    Board Regular
    Join Date
    Nov 2003
    Posts
    960

    Default 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:

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

    HTH

  7. #7
    New Member
    Join Date
    Jul 2004
    Posts
    32

    Default 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. #8
    Board Regular
    Join Date
    Nov 2003
    Posts
    960

    Default Re: Subtract Ranges

    Saving your workbook will update the used range property.

    HTH

  9. #9
    New Member
    Join Date
    Jul 2004
    Posts
    32

    Default Re: Subtract Ranges

    That is pretty silly,
    but it will do.

    Thanks again...

  10. #10
    Board Regular
    Join Date
    Nov 2003
    Posts
    960

    Default Re: Subtract Ranges

    Your Welcome.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com