Delete duplicate row base on 2 cells.(condition)

Try this to see if this works as per your requirement.

Please Login or Register  to view this content.

Hi, sktneer,

i is dimmed as Variant, no need to select a Cell prior to taking comparision (except for slowing the code down), and an alternative to deletion may be

Please Login or Register  to view this content.

Ciao,
Holger

Oh yes. Thanks Holger for pointing this out.
Actually I wrote this line while debugging the code and forgot to remove it. I have edited the code now. Thanks once again.

Please find the attached sheet and click on the Green Button on Test Sheet to see if you get the desired output as you have shown on Sheet2.

Hi, sktneer,

Please Login or Register  to view this content.

dims i and lr as Variant and only lc as long - same as above.

@calvinle:
add an additional column and concatenate the contents to make it the items comparible:

Please Login or Register  to view this content.

No need to sort the data prior to running a macro.

Ciao,
Holger

Originally Posted by HaHoBe

Hi, sktneer,

Please Login or Register  to view this content.

dims i and lr as Variant and only lc as long - same as above.
Ciao,
Holger

Why dim i, lr as Variant and only lc as long? Any logic behind it?
Do you think declaring a variable as a variant, specially when you know that what type of data it will be holding during the code, is a good practice?

No need to sort the data prior to running a macro.

Is there any harm if someone sorts the data if required in order to deleting undesired rows and specially when you don't need to restore the data sequence as it was before running the macro?

I think I got your point.
In Dim i, lr, lc as long VBA will treat lc as a long and i, lr as variant. Right?

While the correct way to declare these variables as long will be as follows.......
Dim i As Long, lr As Long, lc As Long. Correct?

Thanks once again for your guidance and sharing this tip.

Hi, sktneer,

the logic that VBA needs each Variable to be dimmed on itīs own with Variant being the type which may be converted into any other type.

Please Login or Register  to view this content.

As I do have severe problens with typos I rely on Option Explicit and some thoughts on what the Variable is assumed to hold (using Doubles for Row or Column Counters always looks odd to me).

No harm, and nowadays everybody enjoys lots of RAM - no need to count the bytes anymore.

Ciao,
Holger

Point already taken.
Holger! You are a genius.
I really appreciate you for pointing out these siliy mistakes otherwise no one takes interest in sharing his knowledge with others.
Thanks once again.

HI HaHoBe,

What is the column D for? I removed data from this sample file, but there are data in column D. Doing so will delete my column D..

Thanks.

Hi, calvinle,

you would need to refer to the new column throughout the code and not compare the values of the newly concateneted column to Column D.

Please Login or Register  to view this content.

Ciao,
Holger

Hi, calvinle,

I donīt like to answer to threads that are being marked Solved - they either are (then no questions should be open) or arenīt (then they should not be marked Solved).

All you would need to do is insert a third column in the line for evaluate and maybe change the order in which the columns appear.

Please Login or Register  to view this content.

could be changed to read

Please Login or Register  to view this content.

Ciao,
Holger