Dynamic "Columns:=Array(1,2,3)" [duplicate]

Question

I am trying to make a dynamic "Remove Duplicates" macro, but have difficulties making the Columns:=Array(1,2,3) dynamic.

What I have is this:

Sub RemoveDuplicates()
Dim MyArray As Variant
MyArray = Evaluate ("Row(1:"& numOfCol &")")
ActiveSheet.UsedRange.RemoveDuplicates Columns:=MyArray, Header:=xlYes
End Sub

Answer 1

Remove Duplicates

• The 'RemoveDuplicates' array needs to be declared as variant, needs to be zero-based and needs to be evaluated (Evaluate() or just ()).
• Evaluate("Row(1:" & numOfCol & ")") will return a 2D one-column array. If you do an Application.Transpose, it will become a 1D array, but the problem is that it is one-based.

Option Explicit

Sub RemoveDuplicates()
    
    Const NumOfCols As Long = 3
    
    Dim MyArray As Variant: ReDim MyArray(0 To NumOfCols - 1)
    
    Dim n As Long
    
    For n = 1 To NumOfCols
        MyArray(n - 1) = n
    Next n
        
    ActiveSheet.UsedRange.RemoveDuplicates Columns:=(MyArray), Header:=xlYes
        
End Sub