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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top