Forum Discussion
RemoveDuplicates not removing all duplicates
I am using this line of code to remove duplicates from a column in Excel:
Sheet2.Range("H1:H8").RemoveDuplicates Columns:=1, Header:=xlNo
My test data is:
test
test
one
two
three
three
just
checking
three
This is the result:
test
one
two
three
just
checking
three
It will not remove the second instance of "three". This is just the current test data Im using, Ive tried this with several different samples and it will not consistently remove all of the duplicates. It usually always leaves one, like in this example. I have verified and verified and verified again that the words do not have any spaces around them. Im literally at a stand still on a project because of this and it is driving me crazy. Can anyone help?
Thanks,
SJ
H1:H8 contains 8 cells, whereas the last occurrence of "three" would be the ninth. So, just expand the range to H1:H9.
But perhaps better to avoid VBA altogether and format the list of words as a structured Excel table and then use the "Remove Duplicates" button on the Tables ribbon under Tools.
