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:
    [VBA]
    '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
    [/VBA]

    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?

  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?

  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
    [VBA]garbage.clear[/VBA]

    That should take care of it for you
    Matt

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

    [VBA]
    '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
    [/VBA]

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

    [VBA]
    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 If
    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
    [/VBA]

    Using the above 2 functions, which actually work,
    I create the correct Range that I need.

    Cheers!

  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.[vba]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[/vba]HTH

    I've attached an example workbook containing the above.

  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
    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. :-)

Posting Permissions

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