Prevent Duplicate Entries in Excel Automatically with VBA Macro

[@vibhuvarshney1579]

could you help if I need to save data in sheet 2 from sheet 1 and avoid duplicate entries in sheet 2, where I need to compile my data

[@sallytong241]

If it is possible the final action is not deleting the last row, but go to the number that has previously been entered. For example, if it is not ID number, it is fax number, I do not want to type duplicated fax number.

[@ExcelDestination]

for that, I would suggest, you should use search and update feature using vba code.

[@sallytong241]

@@ExcelDestination Thank you

[@muhammadmunir4068]

Respected sir your code is working well when I put duplicate data and press ENTER. But when i move to other cell through arrow keys it does not works.Secondly when we move to previous cell its row also deleted even it is happen to be unique value. Please advise how to fix this issue

[@broudjzed7892]

Hello boss how to update database once by transfering all listbox items once tanks

[@alializadeh8195]

Thank man

[@jooste]

Start @ 2:54

[@sureshsomaiya3820]

Thanks bhai

[@ExcelDestination]

YW

[@曾文東-x5d]

Where to find the exercise file?

[@ranjanbirla5554]

This code has an issue if you move up in column four (04), It will show it is a duplicate record and delete it. You keep moving the cursor in the upper row and it will keep deleting records. Which should not be the case.

[@ExcelDestination]

this code is to prevent duplicates only. if you need any vba code support, please share your requirement at [e-mail]

[@shruthinagappan2678]

Exactly

[@robertgryska3954]

Hi I have problem with this line
lastrow = Worksheets("Sheet1").Cells(Rows.Count, 4).End(x1Up).Row - Runtime error 1004 application defined or object defined error. Thx

[@alsaadventure8501]

XLup not x1up
Cmiiw

[@markarvin7725]

📢 This is nice, but there's a problem. If you mistakenly click on an entry that you don't want deleted, it automatically deletes that entry. Is there a fix out there for this? 😃

[@michaelsmits8677]

why not use a mesbox yes/no to first confirm your action? That would help I guess

[@hazemali382]

Great

[@mukundpotdar9849]

Hi
In case if user try to copy and paste the data from Row 1 of Customer Id to Row 16 of Customer Id.
Can we prevent the duplicate data.

[@ExcelDestination]

Thanks for your question...I haven't tried it while developing this code...will try and let you know.

[@giuseppegenovese4747]

Enter this code and adapt it to the need to do wath you asked for
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nr As Variant
If ActiveCell.Column = 3 And ActiveCell.Offset(1, 0).Value = "" Then
nr = Worksheets("Foglio17").Range("c" & Rows.Count).End(xlUp).Value
lastrow = Worksheets("Foglio17").Cells(Rows.Count, 3).End(xlUp).Row

For i = 2 To lastrow - 1
If nr = Worksheets("Foglio17").Cells(i, 3).Value Then
Worksheets("Foglio17").Cells(lastrow, 3).ClearContents
MsgBox ("Numberr already present please rewrite it"), vbInformation
Worksheets("Foglio17").Cells(lastrow, 3).Select
End If
Next i
End If
End Sub

[@mukundpotdar9849]

Thank you, Its Working

[@luissoler9769]

@@giuseppegenovese4747 Ciao! I've tried this code but when i go to the last cell in the sheet or i have an error i got a massagger with error 13 or error 1004

[@Quoteminds5]

Hello ,
let say we have a id 12 and u right again in the below 12 , then it shows duplicate and delete the cell which is great ,
but when u enter again in blank cell the above data gets deleted also
please advise

[@ExcelDestination]

you can choose any other column, except id.

[@Quoteminds5]

@@ExcelDestination you can choose another column normally , but whats happening if u write id 12 and then press enter it goes to cell 13 , ( which should be id number 13 entered ) but if u leave it blank and press enter again it deletes row 12

[@Quoteminds5]

@@ExcelDestination yes but i want in the id column to not except duplicates and at the same time move down in the empty cells,
2- the rows I'm working with have colors in them , but them i press the command button to copy the rows , the rows copies perfectly as the data specified but the colors of the cells change " is there any vba code to keep the colors the same "

[@crazy2242]

@@ExcelDestination Is there a better way to not delete the cell for that id? What if i select that id column and when i press up, it accidentally deletes the important data one by one?