Creating non-contiguous range without duplicates

[Amateurhr]

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?

 

[Rick Rothstein]

I am guessing this is a VB question, right? You can check if two ranges overlap using the Intersect function...

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.

 

[Amateurhr]

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:

    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