Delete rows where duplicate values found in column A with Excel VBA

upobDaPlaya
upobDaPlaya used Ask the Experts™
on
There seems to be countless ways on how to delete duplicate values found in a column.  What is the most efficient method in VBA if I know I will have a duplicate value in Col A.  Note I do have several other columns, but none of the columns will have data at the point I do the delete in Col A.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
if i did this is this considered sound: ActiveSheet.range("$A$1:$K$410).RemoveDuplicates Columns:=Array(1,2,3,4,5,6,7,8,9,10,11), Header:=xlYes
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you just need to test column A, then your statement might be:
    ActiveSheet.Range("$A$1:$K$410").RemoveDuplicates Columns:=1, Header:=xlYes

Open in new window

Note that the above statement is not deleting entire rows. To do that, you might use:
    ActiveSheet.Range("$1:$410").RemoveDuplicates Columns:=1, Header:=xlYes

Open in new window

And if your range truly has data only in A1:A410, you could use:
    ActiveSheet.Range("$A$1:$A$410").RemoveDuplicates Columns:=1, Header:=xlYes

Open in new window


If you have merged cells, a protected worksheet or formulas that are looking at particular cells in the range--please advise.

Author

Commented:
Initially I did not think I had any merged cells, but when I initially posted I failed to mention that I was getting some type of error when using the below code since it thought I had merged cells.  However, I could not find any..  I will try the above...

ActiveSheet.range("$A$1:$K$410).RemoveDuplicates Columns:=Array(1,2,3,4,5,6,7,8,9,10,11), Header:=xlYes

Author

Commented:
Your solution worked, but it is still very puzzling why it thinks my worksheet has merged cells...In fact there is only 1 column.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial