How to delete the ENTIRE row if only one cell is duplicated?

[sami770]

Friends,
I have a file with 238 rows and 10 columns (see file attached)
Column G include phone numbers, some phone numbers are duplicated.

How can I use a macro (I must do it with a macro) to delete the ENTIRE row if the value in column G is the same in other row?

For example: look at rows 2 and 3 : the value in column G2 and G3 is the same, therefor delete row 3

(and if the value in G4 was also the same, then delete also row 4, and so on..)

[sami770]

Guys, can anyone help? I really need this urgently

[tigeravatar]

sami770,

Give this a try:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[JBeaucaire]

You can use a COUNTIF() formula to test for duplication. In an empty column I would enter this formula and copy down:

=COUNTIF($G$1:$G2,$G2)

Then I would turn on the Data > Filter > Autofilter and do a custom filter on column K for greater than 1.

I would then delete all visible rows, turn off the AutoFilter and clear the added column.

Here's a macro that does it that way:

Please Login or Register  to view this content.

[Mordred]

Hi tigeravatar, I just ran your code and it worked on the example workbook but what I don't get is where you are telling it to compare phone numbers? Is that what CountIf does? I don't mean to jump in on this and ask questions in threads that aren't mine but I was working on this using an array but did not get the desired results (yet).

[Mordred]

Thanks Jerry, you answered my question.

[JBeaucaire]

Glad to help, Mordred.

Tiger, whenever we're doing the same test over and over again on every row, much more efficient to do the tests all at once and delete the rows all at once using an AutoFilter.

[tigeravatar]

@Mordred, hehe, your question was answered before I even read it

@JBeaucaire, yeah, my macro isn't particularly efficient because it tests each cell individually. I took the shortcut because there was only 238 rows which isn't very many. I should strive to maintain efficiency though. Shortcuts are just so attractive sometimes, hehe

[JBeaucaire]

Heh, understood. I'm not swayed by fewer lines of code, my eye is always on the total number of executed commands. Your loop is only 3 lines of code, but it represents 238 commands. That's why I opt for a few more lines of code to get it down to a grand total of 8 commands, even if his code was 10000 rows, still only 8 commands.

I'm a huge fan of the AutoFilter.

[sami770]

Tiger - you macro works perfect!
MANY THANKS

sami770,

Give this a try:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[scaffdog845]

A quick Google search with the site:excelforum.com switch at the end provides yet another answer to a question I had. Thanks guys! And yes... I know it's a old thread.