Delete ranges after duplicate is found

Hello all:

I have a userform that allows a user to enter data from columns A to P. I successfully installed a macro (which I got from this site:http://www.exceltip.com/st/Preventin..._Data/886.html)

that deny a duplicate entry in Column A. The macro then "clears" the duplicate value in column A, which is okey. However, I need help for a code so that the
rest of the entries from Column B to P (within the same row) will also be cleared automatically. I tweaked the "target.value" quite unsuccessfully.

Thanks for the usual help.

WK

Can you post the code you're using?

Originally Posted by StephenR

Can you post the code you're using?

Thanks for the reply. Here's the code:

PHP Code: 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.ScreenUpdating = False

If IsEmpty(Target.Value) Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Column > 1 Then Exit Sub
With Worksheets("Draft").Range(Cells(1, Target.Column).Address & ":" & Cells(Target.Row - 1, Target.Column).Address & "," & Cells(Target.Row + 1, Target.Column).Address & ":" & Cells(Rows.Count, Target.Column).Address)
Set c = .Find(Target.Value, , , xlWhole)
If Not c Is Nothing Then
MsgBox "Preference already exists at range: " & c.Address(0, 0)
Target.Value = ""
Application.ScreenUpdating = True

End If
End With
End Sub 


I've tried target.entirerow.value = "", but it only delete one cell value (the duplicate). hope you can help.

WK

Without looking through all the code I think perhaps just changing this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

might do it...

Originally Posted by StephenR

Without looking through all the code I think perhaps just changing this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

might do it...

Unfortunately, it didn't worked. It just locked excel, like an endless loop has been executed.

Any other idea.

I just did a little test and it worked for me. Can you attach your workbook, or a sample?

Actually just re-read your original question: are you asking to only check for duplicates in column A and then clear B to P or are you asking to check for duplicates in columns other than A?

If the former, change

Please Login or Register  to view this content.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Originally Posted by StephenR

I just did a little test and it worked for me. Can you attach your workbook, or a sample?

Actually just re-read your original question: are you asking to only check for duplicates in column A and then clear B to P or are you asking to check for duplicates in columns other than A?

If the former, change

Please Login or Register  to view this content.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thanks again, and sorry for the confusion. I'm actually working on a scheduling form, which allow a user to select a name and enter the hours worked for the week. The name is entered in column A, and the rest of the data are stored in columns B to P. When the user clicks next (to add new employee) I let the macro check if the employee has already been scheduled. The current macro checks column A for duplicate and clears that duplicate value, which is fine. The other code that I'm looking for is one that could clear the contents of the rest of columns B to P.

I tried both of your code, and the other one works but it seems that the code checks for duplicates from A to P. Surely, the macro only deletes the duplicate, but it should be able to clear the entire row.

Here's a thought: Is there a way that the selected name and the rest of the data would not be written to the worksheet if that name already exists?

Thanks again for your trouble. Much appreciated.

WK

Are you using this code because I think this should work? This will check for any duplicate entry added in column A, and then delete columns A to P in that row.

Please Login or Register  to view this content.

Originally Posted by StephenR

Are you using this code because I think this should work? This will check for any duplicate entry added in column A, and then delete columns A to P in that row.

Please Login or Register  to view this content.

Thanks for the help, but this one doesn't work. I'll just let them manually delete the entry for now. Again, I appreciate your help.

WK

OK, but I'll be happy to look at a sample of your workbook if you like.