Excel Warning With Msgbox If A Column Has Duplicate Values

Excel VBA Msgbox Warning If A Column Has Duplicate Values 


        In this tutorial ,we set our codes according to column A of worksheet.         
When data entered, if there are duplicate values in column A ,with msgbox is warned the user . 

Msgbox shows the repeated data in column A with the row number .If user want, can enter the repeated data or can delete it.

 💡 Our codes:
Private Sub Worksheet_Change(ByVal Target As Range)
        Dim son As Long, onay, bul As String
    Dim ara As Range
        If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
        son = Cells(Rows.Count, "A").End(xlUp).Row
   On Error Resume Next
    If WorksheetFunction.CountIf(Range("A2:A" & son), Target) < 1 Then
    Exit Sub
    End If
    If WorksheetFunction.CountIf(Range("A2:A" & son), Target) > 1 Then
                bul = Empty
        Set ara = Range("A2:A" & son).Find(Target, , xlValues, xlWhole)
        If Not ara Is Nothing Then
            adres = ara.Address
            Do
                bul = bul & ara.Row & "      -      " & Cells(ara.Row, "A") & Chr(10)
                Set ara = Range("A2:A" & son).FindNext(ara)
                Loop While Not ara Is Nothing And ara.Address <> adres
            End If
            onay = MsgBox("Row :        Records :" & vbCrLf & Chr(10) & bul & vbLf & "Do you want to enter?", vbYesNo)
           If onay = vbYes Then MsgBox "Recording has been completed.", vbInformation, "Info"
           If onay = vbNo Then Target.ClearContents
            End If
End Sub

excel warning if a column has duplicate values

No comments:

Post a Comment