Exclude range objects from range

whiteheadw

New Member
Joined
Jul 14, 2010
Messages
8
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.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
Hi

Try:

Code:
' 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:

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

brettdj

Active Member
Joined
Feb 5, 2003
Messages
426
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

New Member
Joined
Jul 14, 2010
Messages
8
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,777
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

New Member
Joined
Jul 14, 2010
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,115
Messages
5,466,769
Members
406,498
Latest member
ddreadedd

This Week's Hot Topics

Top