Solved

Excel Macro to delete duplicate name / row

Posted on 2015-01-22
3
185 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 500 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 47

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now