Link to home
Start Free TrialLog in
Avatar of Nassim am
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)
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Place the code given below on Data Sheet Module.
To do so, follow these steps...
Right click on Data Tab --> View Code and paste the code given below into the opened code window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim x, dict
Dim i As Long, lr As Long, r As Long
If Target.Column = 1 Or Target.Column = 3 And Target.Row > 1 Then
    r = Target.Row
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    If lr < 2 Then Exit Sub
    x = Range("A2:C" & lr).Value
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(x, 1)
        If i <> r - 1 Then
            dict.Item(x(i, 1) & x(i, 3)) = ""
        End If
    Next i
    If dict.exists(Cells(r, 1) & Cells(r, 3)) Then
        Range("A" & Target.Row).Interior.Color = vbRed
        Range("C" & Target.Row).Interior.Color = vbRed
    Else
        Range("A" & Target.Row).Interior.ColorIndex = xlNone
        Range("C" & Target.Row).Interior.ColorIndex = xlNone
    End If
End If
End Sub

Open in new window

You can prevent duplicates using Data Validation, this way no problems will occur if a user does not enable macros

  • 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

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

Open in new window

Avatar of Nassim am
Nassim am

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 :(
SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
For example it still red even i deleted duplicates row . Even i open in other ordinator
Which columns to be checked for duplicates? Column A is blank in the sample file while it is still being used in the code.
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you brother :) it is done now .
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.
Do not be laze to ask . Here you can find what you need . With expert teacher :D