Find duplicate rows of multiple cells...

[spacely]

Hi ...

I have a rectangular arrangement of cells, and want to detect if any row has the same entries as any other row, in the range of rows of interest. A trigger at the end of the duplicate rows, or a conditional format to display which are copies of each other would be great.

So:

1 3 6 4 3 2 1 3
4 5 6 7 3 2 1 4
6 7 8 1 0 8 7 6
4 5 6 7 3 2 1 4

..would flag as row 2 and 4 being identical.

Thanks!

 

[ed9213]

Apologies, I wasn't thinking. It should be

Sub ColourDuplicates()
   Dim Valu As String
   Dim Cl As Range
   Dim UsdCols As Long
   
   UsdCols = Cells(1, Columns.Count).End(xlToLeft).Column
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         Valu = Join(Application.Transpose(Application.Transpose(Cl.Resize(, UsdCols))))
         If Not .exists(Valu) Then
         .Add Valu, Cl.Resize(, UsdCols)
         Else
         Cl.Resize(, UsdCols).Font.Color = vbRed
         .Item(Valu).Font.Color = vbRed
         End If
      Next Cl
   End With

End Sub

=====
Fluff, That was fantastic! It worked great! I then searched for rows with red text and all that needed to be revealed was exposed.
Thanks!
ed9213

 

[Fluff]

Glad to help & thanks for the feedback