Nassim am
asked on
Vba code avoid duplivate
Vba code to avoid writing data in table
a b c d
1 1 2 5 (highlited)
1 1 1 1
1 0 2 7(highlited)
The column A&C is the important . So the data in a+c should not repeat and must highlited or msgbox writen ( data entry are already exist)
Sheet name is ("data") range is (A1:F10000)
a b c d
1 1 2 5 (highlited)
1 1 1 1
1 0 2 7(highlited)
The column A&C is the important . So the data in a+c should not repeat and must highlited or msgbox writen ( data entry are already exist)
Sheet name is ("data") range is (A1:F10000)
You can prevent duplicates using Data Validation, this way no problems will occur if a user does not enable macros
If a duplicate entry is made Excel will reject i. Click Cancel to clear the error message and enter a valid value.
- Select the range of cells in which you want to prevent duplicate values.
- Choose Validation from the Data menu. Click the Data tab and choose Data Validation from the Data Validation option's drop-down list (in the Data Tools Group).
- Click the Settings tab.
- Choose Custom from the Allow drop-down list.
- Enter a formula in the following form into the Formula control:=COUNTIF(COUNTIF($
A$1:$A$50, A1)=1) - Click the Error Alert tab.
- Enter "Caution:Duplicate Entry" in the Title control.
- In the Error Message box, enter a meaningful description, such as £Duplicate entry detected"
- Click OK
If a duplicate entry is made Excel will reject i. Click Cancel to clear the error message and enter a valid value.
The formula above should be >1 not =1
Here's an alternative VBA approach
Here's an alternative VBA approach
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .CountLarge > 1 Then Exit Sub
Dim rRng As Range
Set rRng = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
If Application.WorksheetFunction.CountIf(rRng, .Value) > 1 Then
MsgBox "Duplicate entry"
.Value = Empty
End If
End With
End Sub
ASKER
Subodh Tiwari (Neeraj)
Thank you for answering me
But the code show nothing
I put it in module i writed duplicates wut nothing happened :(
Thank you for answering me
But the code show nothing
I put it in module i writed duplicates wut nothing happened :(
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Roy Cox
Thank for helping me
The code doesnt work
I upload it if you can show the problem
I do kot want to use conditional formatting because data is 20000 row with 5 lines .big data so it crush always
Thank for helping me
The code doesnt work
I upload it if you can show the problem
I do kot want to use conditional formatting because data is 20000 row with 5 lines .big data so it crush always
ASKER
Subodh Tiwari (Neeraj)
That is all what is need may Allah bless you brother
Can i highlite to full row ( a:c ) not each one alone?
That is all what is need may Allah bless you brother
Can i highlite to full row ( a:c ) not each one alone?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Subodh Tiwari (Neeraj)
The work but after highling the rows i delete or change the data to kot be duplicated
But it is highlited :/ even the row is empty
The work but after highling the rows i delete or change the data to kot be duplicated
But it is highlited :/ even the row is empty
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For example it still red even i deleted duplicates row . Even i open in other ordinator
ASKER
Which columns to be checked for duplicates? Column A is blank in the sample file while it is still being used in the code.
ASKER
Ok brother i upload my final work with vba. I tried to edit it but it crushed again so i send it all
The red column (D) and (H) are the criteria
( I said A and C ) i tried to edit to d and h but is seems not working
. Sorry again
LAST-DUPLICATED.xlsm
The red column (D) and (H) are the criteria
( I said A and C ) i tried to edit to d and h but is seems not working
. Sorry again
LAST-DUPLICATED.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you brother :) it is done now .
It is really a good faver for me :D
It is really a good faver for me :D
You're welcome Nassim! Glad it worked as desired.
Please take a minute to close your question by accepting the answer.
Please take a minute to close your question by accepting the answer.
ASKER
Do not be laze to ask . Here you can find what you need . With expert teacher :D
To do so, follow these steps...
Right click on Data Tab --> View Code and paste the code given below into the opened code window.
Open in new window