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

Asked Apr 02 '20 at 02:56

Active Apr 02 '20 at 03:11

Viewed 784 times

I am new to VBA. Currently I am trying to remove duplicate rows from a non-header spreadsheet where, 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. Trying the same using Application.Evaluate function to create a sequence of Array from first to last column of spreadsheet.

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?

edited Apr 02 '20 at 03:00

BigBen

46,229
7
24
40

asked Apr 02 '20 at 02:56

The Flash

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 – The Flash Apr 03 '20 at 00:28
The above way solved the problem. – The Flash Apr 03 '20 at 00:31

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

0 Answers0