Deleting "duplicate" rows
 

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

There are duplicate rows of peoples names which I need deleted, but all
the steps I have found online only sort on one collumn and assuming I am
sorting by Last Name, they will delete not only extra rows of a specific
person's name, but also other people (which would be terrible) whom have
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) 1
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 deleting
the extra entries of a person's name?

Thanks for thr advice,
The N00bish


--
mmednick
------------------------------------------------------------------------
mmednick's Profile: http://www.excelforum.com/member.php...o&userid=29404
View this thread: http://www.excelforum.com/showthread...hreadid=500248

Deleting "duplicate" rows
 

Is my problem too boring or did i word it badly?


--
mmednick
------------------------------------------------------------------------
mmednick's Profile: http://www.excelforum.com/member.php...o&userid=29404
View this thread: http://www.excelforum.com/showthread...hreadid=500248

Deleting "duplicate" rows
 

Why didn't the code you posted work?

Were you in the correct column when you started?

mmednick wrote:

Is my problem too boring or did i word it badly?

--
mmednick
------------------------------------------------------------------------
mmednick's Profile: http://www.excelforum.com/member.php...o&userid=29404
View this thread: http://www.excelforum.com/showthread...hreadid=500248

--

Dave Peterson

Deleting "duplicate" rows
 

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


--
mmednick
------------------------------------------------------------------------
mmednick's Profile: http://www.excelforum.com/member.php...o&userid=29404
View this thread: http://www.excelforum.com/showthread...hreadid=500248

Deleting "duplicate" rows
 

bump, I still need help


--
mmednick
------------------------------------------------------------------------
mmednick's Profile: http://www.excelforum.com/member.php...o&userid=29404
View this thread: http://www.excelforum.com/showthread...hreadid=500248

Deleting "duplicate" rows
 

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.

mmednick wrote:

bump, I still need help

--
mmednick
------------------------------------------------------------------------
mmednick's Profile: http://www.excelforum.com/member.php...o&userid=29404
View this thread: http://www.excelforum.com/showthread...hreadid=500248

--

Dave Peterson

Deleting "duplicate" rows
 

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?


--
mmednick
------------------------------------------------------------------------
mmednick's Profile: http://www.excelforum.com/member.php...o&userid=29404
View this thread: http://www.excelforum.com/showthread...hreadid=500248

Deleting "duplicate" rows
 

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.

mmednick wrote:

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?

--
mmednick
------------------------------------------------------------------------
mmednick's Profile: http://www.excelforum.com/member.php...o&userid=29404
View this thread: http://www.excelforum.com/showthread...hreadid=500248

--

Dave Peterson