Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Macro to delete duplicate name / row

Posted on 2015-01-22
3
Medium Priority
?
217 Views
Last Modified: 2015-01-23
Hi,

I need your help in order to have a macro that will look into ColumnA find duplicate pc name and delete the row of the duplicate.  

Can you assist?
0
Comment
Question by:mldaigle1
3 Comments
 
LVL 7

Accepted Solution

by:
Deadman earned 2000 total points
ID: 40564901
Press "Alt + F11" - This will open the Visual Basic Editor

Public Sub DeleteDuplicateRows()

' This macro will delete all duplicate rows which reside under
‘the first occurrence of the row.

‘Use the macro by selecting a column to check for duplicates
‘and then run the macro and all duplicates will be deleted, leaving
‘the first occurrence only.
Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))
Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
    Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = Rng.Cells(R, 1).Value

If V = vbNullString Then
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
Else
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
End If
Next R

EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub


Copy above vba code and paste and save the Excel file.
Now run the macro
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40565029
If you have XL 2007 or later, you don't need a macro as the functionality is already built in...

1) Select all of your list, including the header row
2) Click "Remove Duplicates" on the Data tab of the ribbon
3) Select/Deselect columns until only Column A is left
4) Click OK.
0
 

Author Closing Comment

by:mldaigle1
ID: 40566484
Thanks Deadman, this is exactly what i wanted, the macro works just fine.  I will be able to call that macro at the end of the other one for clean up purposes.


Have a good weekend,

:)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question