Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA for Excel 2005 to delete duplicate rows

Posted on 2011-04-19
12
Medium Priority
?
384 Views
Last Modified: 2012-05-11
Hello,

I need some VBA code to remove duplicate ROWS, taking into account every column that makes up a row. In other words, values in Column A can be the same, values in Column A + Column B can be the same, and so on. What makes a record unique is Columns A + B + C + D + E.

My file will have a different number of records every month, so I will need the code to take that into account also.

Thanks.
0
Comment
Question by:erp1022
11 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35425440
It should be something like this: (change to Header := xlYes if you have header rows)

ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4,5), Header:=xlNo

Open in new window


I hope this helps.
0
 

Author Comment

by:erp1022
ID: 35426477
When I added the above VBA to my spreadsheet and ran it, I get the attached error.
VBA-error.doc
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35426651
What version of Excel do you have?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:erp1022
ID: 35426670
Office 2003
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35426989
OK. The previous code works for Excel 2007 / 2010.  Please try the following for Excel 2003: (It assumes 5 columns for evaluation, the worksheet is the ActiveSheet, and the data has no header row. If the data has headers, comment out all lines marked 'Header')

Sub RemoveDuplicates()
    Columns("H:L").Delete Shift:=xlToLeft
    
    Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ' Header
    
    Range("A1").FormulaR1C1 = "h1" ' Header
    Range("B1").FormulaR1C1 = "h2" ' Header
    Range("C1").FormulaR1C1 = "h3" ' Header
    Range("D1").FormulaR1C1 = "h4" ' Header
    Range("E1").FormulaR1C1 = "h5" ' Header
    
    ActiveSheet.UsedRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H1:L1"), Unique:=True
    
    Columns("A:G").Delete Shift:=xlToLeft
    
    Rows("1:1").Delete Shift:=xlUp ' Header
End Sub

Open in new window

0
 

Author Comment

by:erp1022
ID: 35427396
I have modified your code (see below) becuase my file has headers. When I run the macro,  am now getting a different error. See attached.

Sub RemoveDuplicates()
    Columns("H:L").Delete Shift:=xlToLeft
   
    '  Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ' Header
   
    '  Range("A1").FormulaR1C1 = "h1" ' Header
    '  Range("B1").FormulaR1C1 = "h2" ' Header
    '  Range("C1").FormulaR1C1 = "h3" ' Header
    '  Range("D1").FormulaR1C1 = "h4" ' Header
    '  Range("E1").FormulaR1C1 = "h5" ' Header
   
    ActiveSheet.UsedRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H1:L1"), Unique:=True
   
    Columns("A:G").Delete Shift:=xlToLeft
   
    '  Rows("1:1").Delete Shift:=xlUp ' Header
End Sub



VBA-error-2.doc
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35427671
What line is causing the error?
0
 

Author Comment

by:erp1022
ID: 35461464
0
 
LVL 20

Accepted Solution

by:
ElrondCT earned 800 total points
ID: 35505833
wdosanjos clearly knows functions in Excel that I don't, but sometimes simpler may work better. I handle this kind of thing at a very basic level, which may not be quite as fast, but is probably more bullet-proof and easier to understand:
Sub DeleteDups()
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    RowEnd = Selection.Row
    ColEnd = Selection.Column
    For CurRow = RowEnd To 2 Step -1      ' Go backwards so deletes don't muck up pointer
        FoundDiff = False
        For CurCol = 1 To ColEnd
            If Cells(CurRow, CurCol) <> Cells(CurRow + 1, CurCol) Then
                FoundDiff = True
                Exit For
            End If
        Next CurCol
        If FoundDiff = False Then
            Rows(CurRow).Delete Shift:=xlUp
        End If
    Next CurRow

End Sub

Open in new window

Note that this won't get rid of duplicates that aren't next to each other. I'm assuming that's not a problem. If it is, you should sort the file before looking for duplicates.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35505929
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 800 total points
ID: 35505944
I also like simple.

My suggestion is to start with a manual process using Excel, then record that process.  Unless you need speed or special situations, this is often an easy way to go.

So first I would go to column F and add a formula (starting in F2): =CONCATENATE(A2, B2, C2, D2, E2)
Copy this formula down to the end of the data.
(If the data is not sorted, then put column F in a different sheet then sort it)
Next, go to G2 and add this formula down to match column F: =IF(F2=F1,"","DUP")
Now go to G2 and turn on filtering. In the filter select those items with the word "DUP"
Select all rows of data.
Delete the rows.
Turn off the filter.

If you turned on the macro recorder just before the above steps, you now have your macro to handle the job.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

577 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