PDA

View Full Version : Deselect or Remove from Range?



Ornithopter
07-09-2004, 10:35 AM
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?

Ornithopter
07-09-2004, 11:00 AM
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?

mvidas
07-09-2004, 11:10 AM
Hi Ornithopter,

How are you deleting the garbage area? Try using
garbage.clear

That should take care of it for you
Matt

Ornithopter
07-09-2004, 11:18 AM
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

Ornithopter
07-09-2004, 11:36 AM
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 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


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

Cheers!

mvidas
07-09-2004, 11:57 AM
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!

Ornithopter
07-09-2004, 12:02 PM
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:

mvidas
07-09-2004, 01:14 PM
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 :)

Richie(UK)
07-10-2004, 01:57 AM
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 SubHTH

I've attached an example workbook containing the above.

Ivan F Moala
07-11-2004, 02:47 PM
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

parry
07-11-2004, 06:30 PM
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. :-)