Exclude range objects from range

[whiteheadw]

All,

I have a tough one for you. Does anyone know if there is a way to create a range object that contains a known range, excluding other overlapping range objects.

For example, let's say I have a pivottable called pvTable1 (dim pvTable1, set pvTable1 = ...).

What I would like to do is return pvTable1.Parent.UsedRange while at the same time excluding pvTable1.TableRange1.

Any ideas, as right now it's looking like my alternative will be to design something implementing named ranges instead.

Thanks in advance.

 

[brettdj]

Take a look at my code in this article, "Adding a "Subtract Range" method alongside Union & Intersect" at http://www.experts-exchange.com/A_2974.html

Cheers

Dave

 

[pgc01]

Hi

Try:

' Range Minus operation
Function RangeMinus(r1 As Range, r2 As Range) As Range
Dim rCell As Range
 
For Each rCell In r1
    If Intersect(rCell, r2) Is Nothing Then
        If RangeMinus Is Nothing Then
            Set RangeMinus = rCell
        Else
            Set RangeMinus = Application.Union(RangeMinus, rCell)
        End If
    End If
Next rCell
End Function

Example of test:

Sub test()
Dim rMinus As Range
 
Set rMinus = RangeMinus(Range("A1:C6"), Range("C1:D2"))
MsgBox rMinus.Address(0, 0)
End Sub

 

[whiteheadw]

Perfect! Application.Union is exactly what I was looking for.

 

[brettdj]

While Union will be fine on smaller ranges, a cell by cell test may start hurting timewise as the scope of the ranges you are working with increases

Cheers

Dave

 

[whiteheadw]

I'll keep that in mind. One of the ways that I'm thinking of implementing this, however, is as part of a property in a class designed specifically to store, and eventually combine, an array of range objects. This will allow me to create different objects consisting of arrays of ranges (and their unified counterpart), thus allowing me to use the union method in stages. I'm hoping this will help reduce run-time for my larger projects.

 

[pgc01]

While Union will be fine on smaller ranges, a cell by cell test may start hurting timewise as the scope of the ranges you are working with increases

I agree.


I think, however, that for hundreds or some thousands of cells the Union way is faster, due to the overhead of creating a workbook. This means that for many cases (most cases?) the Union way is perfectly adequate.


For many thousands of cells I also think that the auxilliary workbook (or auxilliary worksheet) is the way to go.


There's also a third method that I've used, similar to the one in your link but done in place, using arrays instead of the auxilliary workbook, but I'd have to know the data. It would be simpler with contiguous ranges.

 

[whiteheadw]

I agree.

There's also a third method that I've used, similar to the one in your link but done in place, using arrays instead of the auxilliary workbook, but I'd have to know the data. It would be simpler with contiguous ranges.

I think your third method is more in line with what I was talking about. Basically, I'm moving the arrays of ranges into a class, and will just be managing different objects of that class. Keeps the code more organized and easier to maintain.