remove lines if achieve sertain criteria in other columns

[is2_egypt]

Dear All

in attached sheet , i want to delete some rows according to below criteria :

- check if two consecutive rows have same value in columns A,B,C ----- >>> remove the row that does not contain 2077 or 2078 in column F (keep the row that contain 2077 or 2078).
- keep the other rows that have no duplication (like rows 3,4,5 for example.

another question , what will be the case if i can not order the rows so that lines have duplication are not consecutive ? (means rows 2 and3 in the test sheet are not consecutive and exist in rows 2 and 11 for example) ?

note that my sheet contains about 800k rows so it is big file already.
Thanks for your support.

[ikboy]

Try this:

Please Login or Register  to view this content.

[is2_egypt]

Hello Ikboy

Thanks brother , it works very good on the sample , i run it for my sheet which contains 700k rows and it is running for more than 2 hours and seems hang.
any possible way to enhance the code performance/speed ?

will it work if duplicate lines are not consecutive ?

[jindon]

Try

Please Login or Register  to view this content.

[is2_egypt]

Wow , Thanks alot Jindon , its amazing ..... it did the job in my sheet (700K rows ) in almost no time + even it did work if there are 3 rows and it deleted the two rows and kept only the one i want . thanks you so much

appreciate your support to explain the code as it is too hard for me to understand how it works as i need to modify it to work for other sheets and other values (other than 2078 and 2078) , in other sheets i need to do the same for 12 and 2078 and 1233 , how can i do so , can you add comment to describe each line ... i really appreciate your support as understanding the code will help me modify it for my other uses.

[jindon]

in other sheets i need to do the same for 12 and 2078 and 1233 , how can i do so

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

+ means "Or" in this case.

It is not physically deleting the row(s), just replacing the data with the data that you want.
Deleting many rows usually takes time.

[is2_egypt]

Thanks Jindon , appreciate your support , when you have time appreciate if you add a comment describe each line what it does...if you dont mind as this code is a little bit advanced for my VBA knowledge..thanks alot.

[jindon]

this code is a little bit advanced for my VBA knowledge..thanks alot.

Then I suggest you first observe how Array/Dictionary works.
Dictionary is in Excel VB help and a lot of web site explaining Arrays.

Without the basic knowledge of above, comments means nothing to you I'm afraid...

[is2_egypt]

i will go for it .... thanks for the advise brother. appreciate really your support.