CODEWITHSUNDEEP
excelvba
Andrew Fine
Andrew Fine

Reputation: 23

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.

Upvotes: 1

Views: 178

Answers (1)

BigBen
BigBen

Reputation: 49998

Perhaps just use Array:

Columns:=Array(Range("S1").Value, Range("T1").Value)

Upvotes: 1

Related Questions