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

Scott Craner · Accepted Answer · 2016-08-23 15:10:40Z

8

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.

answered Aug 23, 2016 at 15:10

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

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

4 Comments

cyboashu Over a year ago

awesome. answer will be accepted as soon as the time permits. :)

GSerg Over a year ago

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.

Scott Craner Over a year ago

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

shihs Over a year ago

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!

Tom · Accepted Answer · 2019-11-28 09:51:34Z

2

Here is my take on this:

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
ReDim the array so that its base becomes zero - otherwise RemoveDuplicate will throw error for some reason
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

answered Nov 28, 2019 at 9:51

Tom

416 bronze badges

1 Comment

L42 Over a year ago

For some reason, out of all the answers here, this one actually did the trick. Changing the array base to 0 instead of 1 is what made it work for me.

Maciek Keicam · Accepted Answer · 2023-06-05 17:51:34Z

0

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

answered Jun 5, 2023 at 17:51

Maciek Keicam

1

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related