Excel VBA - Get rid of duplicate values in a particular column

Question

I have a worksheet with many rows of IDs.

I would like to know the best way to write a VBA procedure that will look at the range of values in one column, and replace the entire range with only the unique values which appear in that range. So a column of 1000 IDs might reduce down to a column of 150 unique IDs. I would not like this procedure to affect the data in other columns in the worksheet.

So, say the initial column A was:

*IDs*
ID12
ID12
ID34
ID56
ID78
ID78
ID78

I would like it to replace the column with a new column A:

*IDs*
ID12
ID34
ID56
ID78

Thank you kindly.

Note: I know how to do this manually a few different ways, but I would like to cycle through and do this procedure for every non empty column on a sheet, and the columns are of varying length.

Peter L. · Accepted Answer · 2013-02-10 16:50:00Z

1

To achieve what you want please do the following:

Select entire column you want to remove dupes of.
Go Ribbon Data > Remove Duplicates.
Set My data has headers (according your input).

You're done. If you want these steps in VBA - turn on macro-recorder before the start (bottom left corner of Excel window).

answered Feb 10, 2013 at 16:50

Peter L.

7,3445 gold badges37 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1893148 Over a year ago

Hi, thank you but I understand how to do this manually :/. I am trying to write in VBA but using the macro recorder doesn't do the job if I have want to do this same procedure for multiple columns of varying lengths.

Peter L. Over a year ago

@user1893148 column size does not matter, even though range may be changed. Macro-recorded code is a good start for ended solution - all you need is to add loop. However, if you tend to complicate rather than simplify, or perhaps this is for VBA practice - choice is yours buddy, good luck!

Collectives™ on Stack Overflow

Excel VBA - Get rid of duplicate values in a particular column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related