Deleting "duplicate" rows

  • Thread starter Thread starter mmednick
  • Start date Start date
M

mmednick

I have a spreadsheet with 4 collumns: First Name, Last Name, Dept, an
Mailing Address.

There are duplicate rows of peoples names which I need deleted, but al
the steps I have found online only sort on one collumn and assuming I a
sorting by Last Name, they will delete not only extra rows of a specifi
person's name, but also other people (which would be terrible) whom hav
the same last name with a different first name if that makes sense.

The macro I have is:

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) >
Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Can anyone make a suggestion to change this or a new method of deletin
the extra entries of a person's name?

Thanks for thr advice,
The N00bis
 
Why didn't the code you posted work?

Were you in the correct column when you started?
 
It didn't work because it also deletes people who have the same last
name, but different first names, which I def do NOT want to happen.

the Noobish
 
Insert a helper column and use a formula to include both names:

=a1&"----"&b1
and drag down.

Then use your existing code to delete duplicates based on that column.
 
I did what Dave suggested in terms of making a "helper" row, but when i
tired to run the macro excel crashed and now when i try to run it again
I just get an error message about Macro Security.

Any suggestions?
 
What does crash mean?

And make sure you allow macros to run:
tools|macro|security|security level tab

If you opened the file with macros disabled, close and reopen the workbook with
macros enabled.
 
Back
Top