Finding duplicates in a column, prompt user then delete the current one

I have this so far, which is referenced from someone else, but it deletes the entire column up to the header column. Is there any way that I just have the current row deleted as soon as it detects a duplicates?

Please Login or Register  to view this content.

Hi,

great.

Actually, I would like the entire row to clear the contents please.

So something like range (cells 1) to ( cells 60) . Value = ""

possible?

Clear cell contents if the next value you enter in col a has a duplicate.

Only checking column a please.

oh I see..

hmm maybe deleting the entire row is a better option. I will check it tomorrow.

Thank you

@ DaveJapan,

Perhaps the notes I have made in your WB attached, will clear up the misunderstanding.

Thank you for your patience!

Hi JapanDave,

I think you are just great for sticking with me on this issue, thanks a lot!

I have added to your code, as follows to clear the duplicate row from Column A to Column J:

Please Login or Register  to view this content.

Would there be shorter code to accomplish the same result?

One more thing is, can your Code be changed only to fire if Duplicates are not empty.

As it is at the moment, your code regards an empty Row, if there are two or more, as a duplicate, which in Computer terms may be correct. But the user won't understand it as such! they could just ask; "What is two or more of Nothing?"

@ JapanDave,

That's it! Very kind of you. It works perfectly! I should have thought about shortening the clearing bit, myself, but for some stupid action from my side, just did not think of it! How Dumb can i get??

You have been "Promoted" and now share a spot on my wall for people of NOTE! Congratulations! I don't award everyone with such a pretigeous honor.

Once again, Thank you for your help.

I have also added to your Rep.

Keep well, and keep up the good work!!!

Originally Posted by JapanDave

Thanks Winon, and thanks for the rep. Let me know If I can help you out further?

Lifeseeker, if this thread is solved, please go to advance edit and mark it solved.

Hi JapanDave,

I will call it solved for now as I have not implemented on my system yet, but I trust that it will work out fine. Let me give it a try tomorrow!

Thanks again

Hi JapanDave,

I slightly modified the code to suit my need, but I'm stuck. Please see the attached.

Please Login or Register  to view this content.

In the workbook, I have two worksheets, "main" and "sub" and I'm putting the code in the "Main" worksheet.

What I would like to do:

You see in "sub" worksheet, there is a record 123 with diagnosis of "Neither".

Suppose, now you are entering the record 123 in "Main" without knowing that it has been entered in the "sub.

I would like to find a way to check or alert user that the record 123 has been entered in the "sub" already if the user is now trying to enter the same patient. then, I want excel to actually delete the 123 from the "sub", not from the "main".

Is this doable?


I put

Please Login or Register  to view this content.

so that it doesn't do this every time user moves to the next field. Is this right?

Thank you.

Originally Posted by JapanDave

How is the Main worksheet and sub worksheet linked? At present they are not linked.

Did I have to link the two workbooks in this case?

How do I do so?

Thanks

Hi JapanDave,

I would like the duplicate on the sub be deleted, not the main.

So, say....if 123 already exists in sub, and when user is entering the 123 in the Main without knowing that it is already there in the sub, the system prompt user to delete the 123 from the sub, not from the main.

Possible?

Hey JapanDave,



I want to give you millions of thanks. Wow, this is wonderful.

Thank you.

btw, I've fixed the workbook extract thing. it was the working code. But it didn't work because I had other workbooks open. Excel doesn't seem to like it when other workbooks are already open, strangely.

Hi JapanDave,

Actually, due to changing priority, I would like to tie up the loose end of the other one where users are inputing record in main and it detects duplicates against the sub. (your code worked outside my system, but not inside)

After i put your code in my system and put in a test record in main, it then started looping infinitely until i got a message saying over stack and something like outside something something failed...can't recall exactly, then the system froze.

the code is in the worksheet_change event and whereas your code says "A" i changed it to "E" because col E is the look up column.

Should this have happened normally?

can you simply put the code in the worksheet_change event?

I have made sure that i got rid of the application.enableevents pair because i'm just adding these lines into other lines.

Any ideas?


Sorry to bother you again!