1

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?

Improve this question
2
  • 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 Commented Apr 3, 2020 at 0:28
  • The above way solved the problem. Commented Apr 3, 2020 at 0:31
Related questions
651
How can I avoid using Select in Excel VBA?
606
Is there a way to crack the password on an Excel VBA Project?
0
Using Dynamic Ranges in excel
Load 4 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.