Consulting

Results 1 to 11 of 11

Thread: Deselect or Remove from Range?

  1. #1

    Deselect or Remove from Range?

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

    I tried the following 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?
    Last edited by Aussiebear; 04-29-2023 at 08:46 PM. Reason: Adjusted the code tags

  2. #2
    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?
    Last edited by Aussiebear; 04-29-2023 at 08:47 PM. Reason: Adjusted the code tags

  3. #3
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hi Ornithopter,

    How are you deleting the garbage area? Try using
    garbage.clear
    That should take care of it for you
    Matt
    Last edited by Aussiebear; 04-29-2023 at 08:47 PM. Reason: Added code tags

  4. #4
    This is what I am trying... doesn't work.
    The UsedRange is still larger than the PrintArea:


    'Remove anything that is not in the PrintArea
         Dim garbage As Range
         Set garbage = SubtractRanges.subractRanges(ActiveSheet.UsedRange, _
    Range(ActiveSheet.PageSetup.PrintArea))
         garbage.Clear
         garbage.Delete
    ActiveWorkbook.Save
         ActiveSheet.UsedRange.Select
         Exit Sub
    Last edited by Aussiebear; 04-29-2023 at 08:47 PM. Reason: Added code tags

  5. #5
    I have chosen to use the following instead of
    the Flawed Used Range approach:


    Function lastColumn(ws As Worksheet) As Long
    Dim findRange As Range
    Set findRange = ws.Cells.Find(what:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns)
    If findRange Is Nothing Then
        lastColumn = 0
    Else
        lastColumn = findRange.column
     End I
    End Function
     
    Function lastRow(ws As Worksheet) As Long
    Dim findRange As Range
    Set findRange = ws.Cells.Find(what:="*", _
    after:=ws.Range("A1"), _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows)
    If findRange Is Nothing Then
       lastRow = 0
    Else
        lastRow = findRange.Row
    End If
    End Function
    Using the above 2 functions, which actually work,
    I create the correct Range that I need.

    Cheers!
    Last edited by Aussiebear; 04-29-2023 at 08:48 PM. Reason: Adjusted the code tags

  6. #6
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hmm, not sure why the .clear didnt work for you, but you got the issue resolved anyways!

    I like that subractrange function, that may be useful for me once in a great while!

  7. #7
    Kewl... yah I'm slowly building a little library of
    useful snippets. I think I will post them on my
    website when I have a few more interesting ones

  8. #8
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    I've got a huge .xla file with all my little snippets like this that I've made, although I put this one in an .xla file I have with code I didn't write

  9. #9
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi guys,

    Just returning to the original query - subtracting one range from another or, as it is sometimes called, establishing the anti-range.

    The following functions were code by John Walkenbach and Vasant Nanavati. I added a simple 'Tester' routine to show how they work - this will show the original range, then the one to be 'subtracted' and then the range that is left.

    Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
    
    Function InRange(rngInner As Range, rngOuter As Range) As Boolean
    'From John Walkenbach
        If rngInner.Parent.Parent.Name = rngOuter.Parent.Parent.Name Then
        If rngInner.Parent.Name = rngOuter.Parent.Name Then
            If Union(rngInner, rngOuter).Address = rngOuter.Address Then
                InRange = True
            End If
        End If
        End If
    End Function
    
    Function AntiRange(rngInner As Range, rngOuter As Range) As Range
        Dim C As Range
        If InRange(rngInner, rngOuter) Then
        For Each C In rngOuter.Cells
            If Intersect(rngInner, C) Is Nothing Then
                If AntiRange Is Nothing Then
                    Set AntiRange = C
               Else
                    Set AntiRange = Union(C, AntiRange)
               End If
            End If
        Next
        End If
        'MsgBox AntiRange.Address
    End Function
    
    Sub Test()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Dim i As Integer
    With Sheet1
        Set rng1 = .Range("A1:J30")
        For i = 1 To 3
            rng1.Interior.ColorIndex = 36
            Sleep 500
            rng1.Interior.ColorIndex = xlColorIndexNone
            Sleep 500
        Next
        .Range("M2").Value = rng1.Address
        'the 'outer' range
        Set rng2 = .Range("D11:G20")
        For i = 1 To 3
            rng2.Interior.ColorIndex = 45
            Sleep 500
            rng2.Interior.ColorIndex = xlColorIndexNone
            Sleep 500
        Next
        .Range("M4").Value = rng2.Address
        'the 'inner' range
        Set rng3 = AntiRange(rng2, rng1)
        .Range("M6").Value = rng3.Address
        'the 'outer' less the 'inner'
        For i = 1 To 3
        rng3.Interior.ColorIndex = 1
        Sleep 500
        rng3.Interior.ColorIndex = xlColorIndexNone
        Sleep 500
    Next
    End With
    Set rng1 = Nothing
    Set rng2 = Nothing
    Set rng3 = Nothing
    End Sub
    HTH

    I've attached an example workbook containing the above.
    Last edited by Aussiebear; 04-29-2023 at 08:55 PM. Reason: Adjusted the code tags

  10. #10
    VBAX Contributor Ivan F Moala's Avatar
    Joined
    May 2004
    Location
    Auckland New Zealand
    Posts
    185
    Location
    Quote Originally Posted by Ornithopter
    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?
    Reset the UsedRange via code

    Activesheet.UsedRange
    Last edited by Aussiebear; 04-29-2023 at 08:55 PM. Reason: Added code tags
    Kind Regards,
    Ivan F Moala From the City of Sails

  11. #11
    VBAX Regular
    Joined
    Jul 2004
    Location
    Wellington, New Zealand
    Posts
    37
    Location
    Hi, Ivan put me straight on the UsedRange a while ago so to expand on that theme heres some info. You can correct me if Im wrong Ivan!

    The UsedRange for each sheet is defined when a workbook is first opened. Actions which may affect the Used Range such as deleting rows or clearing cells will not automatically refresh the UsedRange, so methods such as Cells.UsedRange or Cells.SpecialCells(xlCellTypeLastCell) etc are not reliable. Instead you must use UsedRange at sheet level, being ActiveSheet.UsedRange on a line prior to using SpecialCells etc or included in the object such as
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).
    Hope this explains this a bit and doesnt add to any confusion. :-)
    Last edited by Aussiebear; 04-29-2023 at 08:57 PM. Reason: Added code tags

Posting Permissions

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