CODEWITHSUNDEEP
excelvbaduplicates
Santhosh Arockiaxavier
Santhosh Arockiaxavier

Reputation: 31

VBA Excel-How to remove duplicates based on three columns

I want to remove duplicate rows based on three columns in a table in excel. When I pass the columns as direct values to an array say example columns 1, 61 and 122 it works fine like in the code below:

cur.Range("data[#All]").RemoveDuplicates Columns:=Array(1, 61, 122), Header:=xlYes

But when I try to pass column values in the array dynamically by finding their column number in the header, it neither throws any error nor removes the duplicates below code is the dynamic one for passing the columns

Set employeeCell = cur.Range("1:1").Find(What:="employee id") Set customerCell = cur.Range("1:1").Find(What:="customer") Set dateCell = cur.Range("1:1").Find(What:="date")
cur.Range("data[#All]").RemoveDuplicates Columns:=Array(employeecell.column,customercell.column, datecell.column), Header:=xlYes

Kindly help on this. I want to remove duplicates based on three columns dynamically by finding their respective column numbers.

Upvotes: 2

Views: 779

Answers (1)

BigBen
BigBen

Reputation: 50162

Try using the ListColumn.Index of each column:

Dim dataTable as ListObject
Set dataTable = cur.ListObjects("data")

Dim employeeIndex as Long
employeeIndex = dataTable.ListColumns("employee id").Index

Dim customerIndex as Long
customerIndex = dataTable.ListColumns("customer").Index

Dim dateIndex as Long
dateIndex = dataTable.ListColumns("date").Index

dataTable.Range.RemoveDuplicates Columns:=Array(employeeIndex, customerIndex, dateIndex), _
                                 Header:=xlYes

Upvotes: 2

Related Questions