CODEWITHSUNDEEP
excelvba
toby
toby

Reputation: 45

Remove Duplicates from a table headers in Excel in vba

I'm trying to remove multiple duplicates in Excel 2016 VBA from a imported XML file.

It works with a array:

ActiveSheet.Range("%tableName%").RemoveDuplicates Columns:=Array(8, 10, 12, 26, 40), Header:=xlYes

But I the problem is that I don't always know what column my data I want to remove duplicates from is. this week it could be 8, 10, 12, 26, 40 next week it could be 9, 10, 15, 26, 40.

It is always the same table header names: 

'8 = Range("%tableName%[udsendelses_dato]")
'10 = Range("%tableName%[start_tid]")
'12 = Range("%tableName%[udsendelses_titel]")
'26 = Range("%tableName%[Titel]")
'40 = Range("%tableName%[Varighed]")

Upvotes: 3

Views: 1760

Answers (1)

A.S.H
A.S.H

Reputation: 29332

There might be simpler but since it is a Table (a ListObject in Excel VBA), this should do:

With Sheet1.ListObjects("Table1")
    .Range.RemoveDuplicates Columns:=Array( _
        .ListColumns("udsendelses_dato").index, _
        .ListColumns("start_tid").index, _
        .ListColumns("udsendelses_titel").index, _
        .ListColumns("Titel").index, _
        .ListColumns("Varighed").index), _
        Header:=xlYes
End With

Upvotes: 4

Related Questions