Excel VBA remove duplicates

Hi All,

I have a bit of code to remove duplicates as follows:

 ActiveSheet.Range(Cells(1, 1), Cells(lastrow, 25)).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlYes

This works fine but sometime there are not 5 columns of data so the macro breaks.

Following this I was wondering, assuming I have ColNo as the variable representing the number of columns in the array, what I would have as the code?
Who is Participating?
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try this

ActiveSheet.Range(Cells(1, 1), Cells(lastrow, 25)).RemoveDuplicates Columns:=Evaluate("=column(A1:" & Cells(1, 5).Address & ")"), Header:=xlYes
If the columns actually are not there when empty, you could use the Excel function COUNTA to get the number of columns and either loop to add the column numbers to a string and then use the VBA function SPLIT to move them to an array.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
Saqib Husain, SyedEngineerCommented:
http://37714155 is an answer and will suffice as a solution. The number 5 in the formula may be replaced with a variable as requested.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.