CODEWITHSUNDEEP
vbaexcel
dk9000
dk9000

Reputation: 101

Excel VBA: How To Remove Duplicate Values From Table?

Thoroughly searched StackOverflow and Google but didn't find answer to this.

Trying to remove duplicate values from a table in Excel (not just spreadsheet)

Current code:

Sub RemoveDuplicates()
ActiveSheet.ListObjects("Table1").ListColumns(1).DataBodyRange.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Issue:

This code requires the data in EVERY column of the table to be identical before it's considered duplicate. (Discovered this through testing).

Goal:

Check for duplicate values only in column 1 of the table then, when duplicates are found, remove the entire row. How can I achieve this?

Upvotes: 3

Views: 17906

Answers (7)

Chi1ink
Chi1ink

Reputation: 1

I just record a macro in excel and remove the duplicates in my table in one column manualy trough the "Data Tools" option in the toolbar, and that was the result, let me know if it work for you all too :)

Sub RemoveDuplicates()
  ActiveSheet.Range("TbIstFiles5[#All]").RemoveDuplicates Columns:=12, _
  Header :=xlYes
End Sub

Upvotes: 0

Ruthie09
Ruthie09

Reputation: 47

Assuming your table is named "Data", and the column to look at is "NumCode", this code works and is easy:

    With ActiveWorkbook.Worksheets("Raw Data").ListObjects("Data")
        .Range.RemoveDuplicates Columns:=Array(.ListColumns("NumCode").Index), Header:=xlYes
    End With

With this, it doesn't matter where the column is in the table and if it gets moved (adding or removing other columns), it doesn't matter.

Upvotes: 0

KarlF
KarlF

Reputation: 1

I have had a similar problem. In the Microsoft Docs the Columns:= parameter is defined as array. After changing the parameter in my code to Columns:=Array(1,1) the result was as expected. My code now is:

RefDest.DataBodyRange.RemoveDuplicates Columns:=Array(1, 1), Header:=xlNo

Hope that helps.

Upvotes: 0

dk9000
dk9000

Reputation: 101

Found solution! Actually, it's a bit of a workaround. I researched 20 different code options for ".RemoveDuplicates" and had zero success with any of them. Here's the code

Sub sbFindDuplicatesInColumn2()
    Dim lastRow As Long
    Dim matchFoundIndex As Long
    Dim iCntr As Long
    lastRow = Range("A65000").End(xlUp).Row

For iCntr = 1 To lastRow
  If Cells(iCntr, 1) <> "" Then
    matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)
    If iCntr <> matchFoundIndex Then
            Cells(iCntr, 2).EntireRow.Delete xlShiftUp
            'Line below is because when a row is deleted the row numbers change so the next row is skipped via the counter unless we have the line below
            iCntr = iCntr - 1
   End If
  End If
Next

End Sub

Notes on the code:

  • Works whether sheet has a table or not
  • Keeps first instance of duplicate and removes the rest (make sure your table is first sorted properly!)
  • Checks for duplicates only in the desired column yet will delete the entire row

Took so much time for something so simple. I hope this will help others!

Upvotes: 1

user4039065
user4039065

Reputation:

Try,

ActiveSheet.ListObjects("Table1").Range.RemoveDuplicates Columns:=1, Header:=xlYes

enter image description here

Upvotes: 0

QHarr
QHarr

Reputation: 84455

Or

Option Explicit

Sub RemoveDuplicates()
   ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33672

Try the code below:

Option Explicit

Sub RemoveDuplicates()

Dim TblRng As Range

Set TblRng = ActiveSheet.ListObjects("Table1").Range        
TblRng.RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

Upvotes: 3

Related Questions