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

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

[@jooste]

Start @ 2:54

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

[@broudjzed7892]

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

[@曾文東-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

[@alializadeh8195]

Thank man

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

[@sureshsomaiya3820]

Thanks bhai

[@ExcelDestination]

YW

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

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

[@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?

[@hazemali382]

Great