Option Explicit
Sub TestMacro()
Dim c As Range
Dim m As Range
Dim r As Range
Dim t As Range
Dim w As Worksheet
Dim lngR As Long
Dim strA As String
Dim j As Integer
Set t = ThisWorkbook.Worksheets("REPEATS").UsedRange
Set w = ThisWorkbook.Worksheets("DETAILS")
w.Cells.ClearContents
w.Range("A1").Value = "DETAILS"
'check rows
w.Range("A3").Value = "ROW REPEATS"
lngR = 4
For Each c In t.Columns(1).Cells
If c = "" Then GoTo NextRow
If Application.CountIf(c.Offset(1, 0).Resize(t.Rows.Count, 1), c.Value) = 0 Then
GoTo NextRow
Else
Set m = c.Offset(0, 0).Resize(t.Rows.Count, 1).Find(c.Value, c)
strA = m.Row
While m.Address <> c.Address
For j = 1 To t.Columns.Count - 1
If m.Offset(0, j).Value <> c.Offset(0, j).Value Then GoTo NextRow
Next j
Set m = c.EntireColumn.FindNext(m)
Wend
End If
w.Cells(lngR, "A").Value = "Row " & c.Row
Intersect(t, c.EntireRow).Copy w.Cells(lngR, "B")
lngR = lngR + 1
w.Cells(lngR, "A").Value = "Row " & strA
Intersect(t, t.Parent.Rows(strA)).Copy w.Cells(lngR, "B")
lngR = lngR + 1
NextRow:
Next c
'check Cells
lngR = lngR + 2
w.Range("A" & lngR).Value = "CELL REPEATS"
lngR = lngR + 1
w.Range("A" & lngR).Value = "VALUE"
w.Range("B" & lngR).Value = "ADDRESSES"
lngR = lngR + 1
For Each c In t.Cells
If c.Value = "" Or Application.CountIf(t, c.Value) = 1 Or Application.CountIf(w.Range("A:A"), c.Value) = 1 Then GoTo NextCell
strA = c.Address(False, False)
w.Cells(lngR, "A").Value = c.Value
w.Cells(lngR, "B").Value = strA
Set r = t.Cells.Find(c.Value, c, xlValues, xlWhole)
While r.Address(False, False) <> strA
w.Cells(lngR, w.Columns.Count).End(xlToLeft)(1, 2).Value = r.Address(False, False)
Set r = t.FindNext(r)
Wend
lngR = lngR + 1
NextCell:
Next c
End Sub