Delete identical rows in excel

[Jofx]

Hi,

I'm looking for a Excel VBA code to delete all identical rows in a range (keeping unique rows).
ex:

Col A
A
B
B
C
D
D
D

should give:

Col A
A
C

Any idea ?
Thanks

 

[xlbo]

Use the macro recorder to record the following:

add a helper column and enter formula

=countif(A1:A100,A1)

copy formula down to bottom of dataset

add autofilter

custom criteria of > 1

delete ALL rows

remove autofilter

et voila....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Loomah]

Just a minor addition to the above
It may help to make the test range absolute before copying the formula xlbo has given you, ie countif(A$1:A$100,A1).

Also, this link might be of some interest to you

http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

thx Loomah - nice catch

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Jofx]

Thanks Geoff and Loomah
I've have to try it now.