VBA Msgbox to show duplicate entries in column

[darthbane]

hello all,
i have been searching quite some time for this but have not found a VBA script that meets my needs. hoping you can help.

I am looking to have a button at the top of my sheet when clicked, will search col. F in sheet8 for any duplicates. If duplicate entries are found it will display the msgbox with the list of duplicate values in col.f, if no duplicates are found it will simply say "no duplicates found"

so far I have been able to find this but it does not work as it is showing 'no duplicates found'
note: there are duplicates in my dataset currently.

Sub test()
    Dim a, i As Long, e
    a = Range("F1", Range("F" & Rows.Count).End(xlUp)).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(a, 1)
            If a(i, 1) <> "" Then .Item(a(i, 1)) = .Item(a(i, 1)) & vbLf & a(i, 1) & " F" & i
        Next
        For Each e In .keys
            If Not .Item(e) Like "*" & vbLf & "*" & vbLf & "*" Then .Remove e
        Next
        MsgBox IIf(.Count > 1, "Found dup" & vbLf & Join(.items, vbLf), "No dup")
    End With
End Sub

the col i am looking for duplicates is part of a table, and the name of the col is 'Uniq Id'. if that helps.

 

[DanteAmor]

Try this:

Sub Show_Duplicate()
    Set r = Range("F1", Range("F" & Rows.Count).End(xlUp))
    For Each c In r
        If WorksheetFunction.CountIf(r, c) > 1 Then If InStr(1, s, c) = 0 Then s = s & "," & c
    Next
    MsgBox IIf(s <> "", "Found dup" & vbLf & Mid(s, 2), "No dup")
End Sub

 

[darthbane]

Works perfectly! thank you soo much again Dante!!

i have one small ask, when the msgbox appears with the duplicate ids. Instead of having them separated by a comma, is it possible to show each one on a separate line?

 

[DanteAmor]

Check if this is how you need it

Sub Show_Duplicate2()
    Dim r As Range, c As Range, s As String
    Set r = Range("F1", Range("F" & Rows.Count).End(xlUp))
    For Each c In r
        If WorksheetFunction.CountIf(r, c) > 1 Then If InStr(1, s, c) = 0 Then s = s & [B][COLOR=#0000ff]vbCr[/COLOR][/B] & c
    Next
    MsgBox IIf(s <> "", "Found dup" & vbLf & s, "No dup")
End Sub

 

[darthbane]

yup. this is exactly what i was hoping for. thanks so much

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.