VBA: Remove duplicates fails when columns array is passed using a variable

Solution 1:

Put () around the array:

Sub test()

       Dim arrCols As Variant

       arrCols = Array(1, 2)

       '/This here works
       Sheet1.Range("$A$1:$B$10").RemoveDuplicates Columns:=Array(1, 2), Header _
            :=xlYes

       '/ Same code fails when the columns array is passed via variable
       '/ Error 5: Invalid procedure call or argument
        Sheet1.Range("$A$1:$B$10").RemoveDuplicates Columns:=(arrCols), Header _
            :=xlYes

 End Sub

It has to do with what vba is expecting to see.

Solution 2:

Here is my take on this:

1. I use the Evaluate function to create an array of number series - comes in handy if you need a long series as you can just change the column letters to get a different series

2. ReDim the array so that its base becomes zero - otherwise RemoveDuplicate will throw error for some reason

3. Use said workaround putting parenthesis around the array when calling RemoveDuplicate

_

Dim arrayTEMP as Variant
arrayTEMP = Application.Evaluate("column(A:Z)")
ReDim Preserve arrayTEMP(0 To UBound(a) - 1) As Variant
.RemoveDuplicates Columns:=(arrayTEMP), Header:=xlYes