Removing Duplicates VBA - Array Coding

Question

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.

try: columnarray = Application.Transpose(application.transpose(Range("S1:T1").Value)) — Scott Craner
– Scott Craner, Commented Mar 20, 2020 at 19:58

BigBen · Accepted Answer · 2020-03-20 20:24:24Z

1

Perhaps just use Array:

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

answered Mar 20, 2020 at 20:24

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Removing Duplicates VBA - Array Coding

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related