(Solved) – Removing Duplicates VBA – Array Coding

(solved)-–-removing-duplicates-vba-–-array-coding

I am trying to code a VBA program to always remove duplicates based on particular header. So I always want to remove duplicates where the column header is equal to, “Original Destination” and “Final Destination”, but the issue is that sometimes those can be in column A or E depending on the user’s configuration of the data. This is what I think should work:

Sub Macro()

    Dim columnarray As Variant
    columnarray = Range("S1:T1").Value
    ActiveSheet.Range("$A$4:$BV$75000").RemoveDuplicates Columns:=(columnarray), Header:=xlYes

End Sub

S1 contains a match equation that basically spits out what column the “Original Destination” column is in. And T1 is the column where the Final Destination header is. There must be something that I am writing wrong in the array. If S1 contains a value of 2, and T1 cell has a value of 3, I would think that the remove duplicate function would work the same as if I had written

RemoveDuplicates Columns:= Array(2,3)

I get an error saying I have an “Invalid procedure call or argument”. Thank you for your help.

Leave a Reply

Your email address will not be published. Required fields are marked *