Dynamic "Columns:=Array(1,2,3)" in RemoveDuplicates excel VBA

[rahulpriyadarshi3785]

I am new to VBA.
Currently I am using this,
lastColumn is last column of worksheet.
lastRow is last row of worksheet.

It is working fine for:

ActiveSheet.Range(Cells(1,1), Cells (lastRow,lastColumn)). RemoveDuplicates(Columns:=Array(1,2,3), Headers:=xlNo)

- But it will work only for 3 columns each time.

I want to remove duplicate rows from Excel worksheet by making Array dynamically.


MyArray=Evaluate ("Row(1:" & lastColumn & ")")
ActiveSheet.Range(Cells(1,1), Cells (lastRow,lastColumn)). RemoveDuplicates(Columns:=myArray, Headers:=xlNo)

But getting"invalid procedure call or argument" error.

Is there any possible way to do it for a header/ non-header spreadsheet?

 

[rahulpriyadarshi3785]

Dim myArray as Variant

Is used.

 

[Fluff]

How about

   ReDim MyArray(lastcolumn - 1)
   For i = 1 To lastcolumn
      MyArray(i - 1) = i
   Next i
   ActiveSheet.Range(Cells(1, 1), Cells(LastRow, lastcolumn)).RemoveDuplicates (MyArray), xlNo

 

[rahulpriyadarshi3785]

How about

   ReDim MyArray(lastcolumn - 1)
   For i = 1 To lastcolumn
      MyArray(i - 1) = i
   Next i
   ActiveSheet.Range(Cells(1, 1), Cells(LastRow, lastcolumn)).RemoveDuplicates (MyArray), xlNo

Thank you.
Also it works fine.

Dim x as Variant
x = Evaluate (Columns("A":"Z"))
ReDim Preserve x(0 To lcol-1) As Variant
.Range(Cells(1,1),Cells(lrow,lcol)). RemoveDuplicates (x), xlNo

 

[rahulpriyadarshi3785]

Than

How about

   ReDim MyArray(lastcolumn - 1)
   For i = 1 To lastcolumn
      MyArray(i - 1) = i
   Next i
   ActiveSheet.Range(Cells(1, 1), Cells(LastRow, lastcolumn)).RemoveDuplicates (MyArray), xlNo

Thank you Fluff.

 

[Fluff]

You're welcome & thanks for the feedback

 

[HarryS]

Sub DumpaDuplicates(RA As Range, CCC$)    ' columns parameter CCC as CSV   like  3    2,5,4    2,1
   Dim ColDoV() As Variant
   Dim SSA$(), UB&, II&
   SSA = Split(CCC, ","): UB = UBound(SSA)  ' split CCC into an string array => variant array
   If UB >= 0 Then  ' remove them
      ReDim ColDoV(UB)
      For II = 0 To UB
         ColDoV(II) = SSA(II)
      Next II
      RA.RemoveDuplicates ColDoV, xlGuess
   End If
End Sub