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

Question

When the Columns parameter of the RemoveDuplicates is passed using a variable it fails and throws error. Same code works when the columns are passed directly as Array(1,2)

Error 5: Invalid procedure call or argument

 Sub test()

       Dim arrCols

       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

Answer 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.

Answer 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