Clever Geek Handbook

Remove duplicates from table headers in Excel in vba

I am trying to remove multiple duplicates in Excel 2016 VBA from an imported XML file.

It works with an array:

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

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

These are 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]")
+3


source to share


1 answer


Might be simpler, but since this 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
+3


source






More articles:


All Articles