Finding Duplicate Rows (Macro)

  • Thread starter Thread starter Edwin
  • Start date Start date
E

Edwin

Hi,

I have a table with several rows and columns of information. I will
continue to add new information new rows and I want to make sure that I never
have duplicated a row.

So here is a simple example:

A B C
1 i 1 k

2 r 3 n

3 a 2 f

4 a 1 f


I want to be able to add another row of information and if the array in that
row from columns a to c matches an array I already have I want to be notified
and it would also be nice to know which array I am duplicating.

I would like to accomplish the task above, but using the VBA tool (macros).
Ideally I would get the Item Number (located in the first column) of the
array I’m duplicating in a message box.

Any help would be greatly appreciated!

Regards,
 
Here's a simplistic macro that might get you started. After inputting your
data, and having selected a cell in the row you want to check, you could run
this.

Sub FindDuplicate()
xEnd = ActiveCell.Row

For i = 1 To xEnd - 1
If Cells(i, 1).Value = Cells(xEnd, 1) Then
If Cells(i, 2).Value = Cells(xEnd, 2) Then
If Cells(i, 3).Value = Cells(xEnd, 3) Then
MsgBox "Row " & i & " contains duplicate entry.", vbOKOnly, _
"Duplicate Found"
Exit Sub
End If
End If
End If
Next
MsgBox "No duplicate found", vbOKOnly, "No Duplicates"
End Sub
 
Thanks Luke!! This is a great start!

Do you think it is possible to obtain the location of more than one
duplicated row in the table? Maybe using a loop or something like that??
 
Back
Top