5

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
Plutian
  • 2,276
  • 3
  • 14
  • 23
cyboashu
  • 10,196
  • 2
  • 27
  • 46

3 Answers3

7

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • awesome. answer will be accepted as soon as the time permits. :) – cyboashu Aug 23 '16 at 15:12
  • 1
    This is a workaround rather than a fix, because there actually appears to be a problem with the `RemoveDuplicates` method rather than with VBA. It expects an array of Variants, and it (incorrectly) rejects a single variant containing an array, which `arrCols` is, so another workaround would be to declare `Dim arrCols() As Variant` - but for that to work, one would also have to not use the named parameter for `Columns`: `.RemoveDuplicates arrCols, Header:=xlYes` works given `Dim arrCols() As Variant`. VBA is happy to pass an array either way, the error occurs at runtime inside the method. – GSerg Sep 29 '18 at 11:27
  • 1
    This post is over two years old. But sounds like you should put all that in another answer so others who may encounter this problem can see the two different methods. @GSerg – Scott Craner Sep 29 '18 at 15:01
  • In Excel 2016, the array doesn't need `()` and works fine, but it gets error when running the code on office 365. Thanks for solving my problem! – shihs Jun 14 '22 at 06:17
1

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
Tom
  • 31
  • 6
0

Check, if you have option base 1 in top of module. It might affect the code execution apparently.

Maciek Keicam
  • 1