Clear (not Delete) Duplicate Values in Column

[cda]

I feel stupid asking this question - it should be easy but I keep getting a "variable required - can't assign to this expression" error.

I'm trying to evaluate cells in column B. If the value in cell B3 is the same as the value in B2, clear the value in A3 and B3. If the value in cell B4 is the same as B3, clear A4 and B4, and so on until the last cell of the range. Here is what I came up with:

Please Login or Register  to view this content.

I've attached a file that has the original data in one worksheet (source), then the desired result in another (result).

Thanks for any guidance you can provide.

[TMS]

I'd use a helper column but there are probably other/easier ways:

In column H, starting in cell H2:

=IF(AND(COUNTIF(B:B,B2)>1,B2<>B1),"","flag")

Then:

Please Login or Register  to view this content.

Regards

[Marcol]

One way might be

Please Login or Register  to view this content.

Hope this helps

[StephenR]

Have changed your code a little. Better I think to start at the bottom and work up as you are deleting cells.

Please Login or Register  to view this content.

OK, a bit late, but I think your original error was misuse of Set, not needed if you are assigning to the value of a cell, only a range itself.

[martinleyland]

I might be clearing the wrong cells for you, but something like this?

Please Login or Register  to view this content.

[martinleyland]

Gosh - didn't realise I typed so slowly! Or maybe you 3 guys are just lightning

[cda]

Wow, you guys are fast!

StephenR, your code worked beautifully! I'm going to dissect it to see how you did this.

Thank you martinleyland, Marco & TMSchucks as well. I'm learning there is always more than one way to do something in VB, so I will learn from your examples as well.