excelvbaworksheet

Overlapping areas in same range, duplicate cells


Consider the following code:

Dim rng As Range
Dim rngCell As Range

Set rng = ActiveSheet.Cells(2, 2).Resize(3, 3)
Set rng = Union(rng, ActiveSheet.Cells(3, 3).Resize(3, 3))
Set rng = Union(rng, ActiveSheet.Cells(4, 4).Resize(3, 3))

'Shows 27, should be 19
MsgBox rng.Cells.Count

rng.ClearContents
For Each rngCell In rng.Cells
    rngCell = rngCell + 1
Next rngCell

The result is:

enter image description here

Basically the code produces one range variable which holds 3 overlapping areas. When iterating over the cells with For Each, some cells are visited more than once. Also, Cells.Count shows a higher number than the actual number of (unique) cells in the range.

How do I flatten/collapse these 3 areas so that there is no overlapping?


Solution

  • Option Explicit
    
    Sub Demo()
        Dim rng As Range
        Dim rngCell As Range
        Dim mergeRng As Range
        Set rng = ActiveSheet.Cells(2, 2).Resize(3, 3)
        Set rng = Union(rng, ActiveSheet.Cells(3, 3).Resize(3, 3))
        Set rng = Union(rng, ActiveSheet.Cells(4, 4).Resize(3, 3))
        
        'Shows 27, should be 19
        Debug.Print rng.Cells.Count
        Debug.Print rng.Address
        
        For Each rngCell In rng.Cells
            If mergeRng Is Nothing Then
                Set mergeRng = rngCell
            Else
                Set mergeRng = Union(mergeRng, rngCell)
            End If
        Next rngCell
        Debug.Print mergeRng.Cells.Count
        Debug.Print mergeRng.Address
        mergeRng.ClearContents
        For Each rngCell In mergeRng.Cells
            rngCell = rngCell + 1
        Next rngCell
    End Sub