none
Find Duplicate value in a Column RRS feed

  • Question

  • Hi,

    I have some duplicate data in my excel workbook. I want a macro that will notify me if the A column of my workbook has duplicate values. I can easily find them using conditional formatting but I want a piece of vba code that will do the needful. The functionality of the code will return a message "Duplicate" if Column A contains any duplicate values. 

    Thank You.

    Monday, June 22, 2015 5:15 PM

Answers

  • Sub repeatedValues()
    
    Dim value As Variant
    Dim intLastRow
    
    Sheets("NAMEOFYOURSHEET").Select
    
    intLastRow = Sheets("NAMEOFYOURSHEET").Range("A1").SpecialCells(xlLastCell).Row
    
    value = Range("A1").value
    
    For i = 1 To intLastRow
            For j = i + 1 To intLastRow
                If Cells(i, 1).value = Cells(j, 1).value Then
                    If Cells(i, 1).value <> "" Then
                        MsgBox "Repeated value found !", vbInformation + vbOKOnly
                        Cells(j, 1).Select
                        Exit Sub
                    End If
                End If
            Next j
    Next i
    
    MsgBox "No repeated values were found !", vbInformation + vbOKOnly
    
    End Sub

    • Proposed as answer by André Santo Monday, June 22, 2015 6:05 PM
    • Marked as answer by Deb_chatt Monday, June 22, 2015 6:28 PM
    Monday, June 22, 2015 6:05 PM