0

Excel VBA distinguishes between passing an array directly (RemDuplicates1) and passing a variable containing an array (RemDuplicates3) to the method Range.RemoveDuplicates(). Why is it so and how can we assign to a variable an array, so that the variable can be accepted by the RemoveDuplicates method as a parameter.

A workaround is (RemDuplicates2) to reside the array inside a function and return it as the Column parameter of RemoveDuplicates().

Please find my tests below.

Thanks in advance for any hints and help.

Option Base 0
 
' Test environment:
' Microsoft® Excel® for Microsoft 365 MSO (Version 2303 Build 16.0.16227.20202) 64 Bit
' Windows 10 64 Bit

' Selection is of type Range

Sub RemDuplicates1()
    ' This works.

    Selection.RemoveDuplicates Array(1, 2)
End Sub

Sub RemDuplicates2()
    ' This works.
    
    Selection.RemoveDuplicates ReturnArray
End Sub

Function ReturnArray()
    ReturnArray = Array(1, 2)
End Function

Sub RemDuplicates3()
    ' This does not work.

    Dim Arr
    Arr = Array(1, 2)
    Selection.RemoveDuplicates Arr
End Sub
John
  • 35
  • 1
  • 7
  • 1
    `Selection.RemoveDuplicates (Arr)` works. See linked duplicate for previous discussion. – Tim Williams Apr 12 '23 at 23:44
  • @TimWilliams thanks for your quick and helpful answer. For those who have the same problem and read this topic directly: In order to work `RemoveDuplicates` the following two criteria have to be fulfilled: 1. `Option Base 0` has to be set. 2. ' The range has to contain at least the columns passed by the array. Else it throws runtime-error 5: Invalid procedure call or argument. – John Apr 13 '23 at 14:37

0 Answers0