Remove duplicate cells from range

Question

I have a macros that creates a Range of cells named rng. I then use the set rng=rng.entirerow to get a range containing rows for all the cells in the original range. ? rng.address $1:$1,$4:$4,$4:$4,$6:$6,$7:$7,$7:$7

Rows 4 & 7 are listed twice in the range.

How can I identify the duplicates and then redefine the range to only include each row once?

Answer

Steve,

this worked for me

Sub abcd() Dim rng As Range, r As Range, rw As Range Set rng = Range("$1:$1,$4:$4,$4:$4,$6:$6,$7:$7,$7:$7") MsgBox "Before: " & rng.Address For Each rw In rng.Rows

 If r Is Nothing Then
    Set r = rw
 Else
     Set r = Union(r, rw)
 End If

Next Set rng = r MsgBox "After: " & rng.Address End Sub

here is a shorter way to do it:

Sub abcde() Dim rng As Range Set rng = Range("$1:$1,$4:$4,$4:$4,$6:$6,$7:$7,$7:$7") MsgBox "Before: " & rng.Address Set rng = Intersect(rng.EntireRow, Columns) MsgBox "After: " & rng.Address End Sub

Both produced Before: $1:$1,$4:$4,$4:$4,$6:$6,$7:$7,$7:$7 After: $1:$1,$4:$4,$6:$7

tested in Excel 2007, US English.