Delete ranges after duplicate is found

[WhiteKelso]

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

[StephenR]

Can you post the code you're using?

[WhiteKelso]

Can you post the code you're using?

Thanks for the reply. Here's the 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

[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...

[WhiteKelso]

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.

[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.

[WhiteKelso]

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

[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.

[WhiteKelso]

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

[StephenR]

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