1

I want to highlight all the duplicates of a concatenated string in column I and provide an error message if there are any duplicates highlighted. However, there are several blank cells in the column and I do not want these to show up as duplicates when I am running the macro.

I got this code from on here: 

Sub HighlightDuplicateValues()
    Dim myRange As Range

    Range("I1", Range("I1").End(xlDown)).Select

    Set myRange = Selection

    For Each myCell In myRange
        If Not IsEmpty(ActiveCell.Value) = True Then
            If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
                myCell.Interior.ColorIndex = 36
            End If    
        End If   
    Next myCell 
End Sub

I have absolutely no experience in VBA but from what little I understand it seems like it should work. However, what ends up happening is nearly all my data gets deleted. It's rather unfortunate.

Again, I want to highlight any duplicates in the concatenated column I, but I don't want these blank cells to count as duplicates. Having the code for an error message to pop up would be an excellent added bonus, but is not currently my main focus.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
pseevs
  • 21
  • 1
  • 2
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). And you probably meant to use `IsEmpty(myCell.Value)` instead of `ActiveCell` – Pᴇʜ Jul 02 '19 at 13:56
  • 1
    Is there a reason you can't use conditional formatting? – cybernetic.nomad Jul 02 '19 at 13:56
  • @cybernetic.nomad the only reason is that I don't know how to automate that in VBA/ I need to leave out the blanks from the highlighting. If you can do both of those things in conditional formatting in VBA, then I was unaware. – pseevs Jul 02 '19 at 14:07
  • @Pᴇʜ when you're right you're right, thank you – pseevs Jul 02 '19 at 14:09
  • 1
    Let me rephrase: "is there a reason you can't use conditional formatting and are obliged to do this in VBA?" (Because you can do this with conditional formatting alone and, unless other things come into play, don't need VBA to do this at all) – cybernetic.nomad Jul 02 '19 at 14:09
  • you can automate conditional formatting too https://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code – Doug Coats Jul 02 '19 at 14:12
  • @cybernetic.nomad oh, yes- this is part of a larger project I have going in Blue Prism which is supposed to automate a process for us, but I need to have a macro i can execute from within blue prism in order to have it run automatically – pseevs Jul 02 '19 at 14:19

1 Answers1

3

If you want to use VBA this should work for you.

    Dim mydict As Object
    Dim iter As Long
    Dim lastrow As Long
    Dim errmsg As String
    Dim key As Variant

    Set mydict = CreateObject("Scripting.Dictionary")

    ' If you want to use early binding add in the Microsoft Scripting Runtime reference then: Set mydict = new dictionary

    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For iter = 2 To lastrow
            If Not mydict.exists(.Cells(iter, "A").Value) Then
                mydict.Add .Cells(iter, "A").Value, False
            Else
                .Cells(iter, "A").Interior.ColorIndex = 36
                mydict(.Cells(iter, "A").Value) = True 'Keep track of which values are repeated
            End If
        Next
    End With
    errmsg = "Duplicate Values: "
    For Each key In mydict
        If mydict(key) = True Then 'Dupes
            If Not errmsg = "Duplicate Values: " Then 'No extra comma
                errmsg = errmsg & ", " & key
            Else
                errmsg = errmsg & " " & key
            End If
        End If
    Next

    MsgBox errmsg
Warcupine
  • 4,460
  • 3
  • 15
  • 24