How can duplicate RANGES (not values) be removed from a range variable?

0
votes

I have VBA code that loops through each selected cell in Excel. I noticed that in some cases the cell count of the selection was higher than expected. If for example I manually select cells A1:A2 and then CTRL-click cell A2 I was expecting the cell count of the selection to be 2. To test it I ran this code:

Dim target As Range
Set target = Application.Selection
MsgBox target.Cells.Count

...and the cell count was actually three. The selection in this case refers to cells A1:A2 and A2 again.

How can I take a range like this that overlaps with itself and convert it to a range that does not overlap? This is a simple example but actual ranges can be much larger and contain larger overlapping ranges. Thanks!

1
vbaexcel

1 Answers

1
votes

This code works and has been tested.

I am taking out target as variable name since it's Keyword for Worksheet_Event variables.

Option Explicit

Sub DeDupeRange()

    Dim rngSelection As Range
    Set rngSelection = Application.Selection

    Dim rngDeDuped As Range

    Dim rngLoop As Range
    For Each rngLoop In rngSelection

        If rngDeDuped Is Nothing Then
            Set rngDeDuped = rngLoop
        Else
            If Intersect(rngLoop, rngDeDuped) Is Nothing Then
               Set rngDeDuped = Union(rngDeDuped, rngLoop)
            End If
        End If
    Next

    Debug.Print rngSelection.Address 'result is A1:A2,A2
    Debug.Print rngDeDuped.Address 'result is A1:A2

End Sub