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:

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:

Code:

---

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?

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

Code:

---

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?

Hi Ornithopter,

How are you deleting the garbage area? Try using

Code:

---

garbage.clear

---

That should take care of it for you
Matt

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

Code:

---

'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

---

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

Code:

---

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!

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!

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

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

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.

Code:

---

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.

Quote:

---

Originally Posted by Ornithopter

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

Code:

---

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

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

Code:

---

ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).

---

Hope this explains this a bit and doesnt add to any confusion. :-)