PDA

View Full Version : Need help for searching duplicate rows in a worksheet



avadhutd2
01-07-2010, 06:52 AM
Hi,

I need help in dentifying duplicate rows (may be more than one) in a worksheet. I tried searching data over the net for this, but need some specific help.

I had attached a sample worksheet with 5-6 rows, out of these 3 rows are identical.
I need to get a function going that will
1) identify the row & get the duplicate count.
2) neglect this row and go for next row for processing.

Can anyone guide me best possible way to get this done?

Thanks in advance...
Cheers!

Bob Phillips
01-07-2010, 07:11 AM
Public Sub ProcessData()
Const FORMULA_COUNT As String = _
"=SUMPRODUCT(--(B<row>:B<lastrow>=B<row>),--(C<row>:C<lastrow>=C<row>),--(D<row>:D<lastrow>=D<row>),--(E<row>:E<lastrow>=E<row>))"
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To LastRow

If .Evaluate(Replace(Replace(FORMULA_COUNT, "<row>", i), "<lastrow>", LastRow)) = 1 Then

Debug.Print i
'process it
End If
Next i

End With

End Sub