Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows in MS Excel | Excel Discussion (Misc queries) | |||
Deleting rows in a macro in Excel | Excel Discussion (Misc queries) | |||
Question about deleting rows from sorted linked worksheets | Excel Worksheet Functions | |||
Converting formula to value and deleting rows using macros. | Excel Discussion (Misc queries) | |||
Deleting rows containing common data | Excel Discussion (Misc queries) |