Restricting duplicate values in Excel VBA

Question

I need to restrict users entering duplicate values in the columns 2 and 5 compared to the columns 2 and 5 in a previous row

My code restricts entering duplicates if either Column 2 OR Column 5 has duplicates compared to the values for these columns in a previous row.

My goal is to have a warning / action when both columns have duplicate values.

Screenshot example:

VBA code in "Sheet1":

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If (.Column <> 2 And .Column <> 5) Or .Cells.Count > 1 Then Exit Sub

        If WorksheetFunction.CountIfs(Columns(.Column), .Value) > 1 Then
            Application.DisplayAlerts = False
            .ClearContents
            Application.DisplayAlerts = True
            MsgBox "Duplicate value!"
        End If

    End With
End Sub

Please, better clarify what "if BOTH columns are duplicates" should mean. Should it be interpreted as "if the target value already exists once in each these both columns" ? — FaneDuru
– FaneDuru, Commented Dec 28, 2021 at 11:51
@FaneDuru Sorry, "if BOTH columns are duplicates" = if the values entered in column 2 and in column 5 are the same values as in previous row - for these two columns — Hell-1931
– Hell-1931, Commented Dec 28, 2021 at 11:54
Your event code does not allow simultaneous entering in those columns. .Cells.Count > 1 Then Exit Sub stops the code. It should be necessary to better explain what you try accomplishing. In your question you ask about duplicates, now you talk about the "previous row"... Did you want saying previous rows? Meaning existing values in those two columns? Even so, you should be better clarifying what what you want. I must confess that I still not understand what you really need... Where my above interpretation suggestion is wrong? I mean, in my first comment... — FaneDuru
– FaneDuru, Commented Dec 28, 2021 at 12:00
OK. Clearer a little... 1. How many cells does Target contain? I mean isn't only one cell modified (column B OR E)?. 2. If only one cell, but it must be checked against both columns, should it check only the row above the one where the Target exists? 3. Should the event allow a Targed consisting of a discontinuous range with two cells (Range("B3, E3"))? If not the row above to be the reference, your example in not so elocvent... — FaneDuru
– FaneDuru, Commented Dec 28, 2021 at 12:14

CDP1802 · Accepted Answer · 2021-12-28 14:11:41Z

Use Find, FindNext on column 2 and then check the value in column 5. Note - this will find duplicate in any row not just the previous.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const PK1 = 2 ' Primary Keys
    Const PK2 = 5

    Dim V1, V2, rng As Range, first As String
    
    If (Target.Column = PK1) Or (Target.Column = PK2) Then
        V1 = Me.Cells(Target.Row, PK1) ' B
        V2 = Me.Cells(Target.Row, PK2) ' E
        If V1 = "" Or V2 = "" Then Exit Sub
    Else
        Exit Sub
    End If
    
    With Me.Columns(PK1)
        Set rng = .Find(V1, lookat:=xlWhole, LookIn:=xlValues)
        If Not rng Is Nothing Then
            first = rng.Address
            Do
               If (rng.Row <> Target.Row) And (Cells(rng.Row, PK2) = V2) Then
                  MsgBox "Duplicate Value " & V1 & "," & V2, vbExclamation, "Row " & rng.Row
                  Target.Select
                  Application.EnableEvents = False
                  Target.Clear
                  Application.EnableEvents = True
                  Exit Do
               End If
               Set rng = .FindNext(rng)
            Loop While rng.Address <> first
         End If
    End With
    
End Sub

Collectives™ on Stack Overflow

Restricting duplicate values in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related