We help IT Professionals succeed at work.

Excel VBA  remove duplicates

matt_m
matt_m asked
on
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?
Comment
Watch Question

Commented:
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.
CERTIFIED EXPERT
Commented:
Try this

ActiveSheet.Range(Cells(1, 1), Cells(lastrow, 25)).RemoveDuplicates Columns:=Evaluate("=column(A1:" & Cells(1, 5).Address & ")"), Header:=xlYes
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
CERTIFIED EXPERT

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.