Creating non-contiguous range without duplicates

Amateurhr

Active Member
Joined
Dec 26, 2011
Messages
343
I am trying to create a large range composed of smaller ranges. Some of these ranges may overlap so I want to make sure when I add cells to the range they don't already exist. I know how to make sure the same value doesn't exist in a range, but not the same cell. Any ideas?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I am guessing this is a VB question, right? You can check if two ranges overlap using the Intersect function...

Code:
If Intersect(Range1, Range2) Is Nothing Then
    '  They do not overlap
Else
    '  The overlap
End If
If an overlap is indicated, then you can iterate the "new" range cell by cell checking if that cell intersects with the "old" range and, if not, use the Union function to join it to the "old" range. However, if you have a lot of cells, this might get very slow as Union tends to get slower and slower as it gets used more and more. Maybe if you give us an example of some ranges you want to unionize, and tell us what you will do with that range once formed, perhaps we can offer you an alternative approach if one exists.
 
Upvote 0
Hi Rick,

I think your solution should be managaeable as the range I am checking is relatively small (one row of cells). Here's the code I have in case you have optimization ideas. I am looking at a starting range of recordRowRng and trying to build another range called evalRng. The other idea is that I can put this all into a 2 dimensional array with knowledge of the values/addresses, but that seems unnecessary for this amount of data:

Code:
    Set recordRowRng = DynamicRangeH(Sheets(dictData(record)("sheet")), uidItemRng.row)
    
    For Each cellRng In recordRowRng
        
        Set tempRng = cellRng.Dependents
        For Each tempCellRng In tempRng

            'Ignore all dependents on the same row
            If Not tempCellRng.row = uidItemRng.row Then
                
                If RangeIsBlank(evalRng) Then
                    Set evalRng = tempCellRng
                ElseIf Intersect(tempRng, tempRng) Is Nothing Then
                    Set evalRng = Union(tempRng, tempRng)
                End If
                
            End If
        Next tempCellRng
        
    Next cellRng
 
Upvote 0

Forum statistics

Threads
1,211,839
Messages
6,104,296
Members
447,901
Latest member
boy3hc2004

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