Checking for Duplicates - Concatenating within Cells

Question

Morning everyone,

I had a question about using VBA to check for duplicates and concatenate data. Right now I have my data set up to display as:

City-Municipality-County

However, there's a ton of cities in different counties with the same name. I'm still new to VBA, but I found a code for VBA that checked within a column for duplicates and then typed "duplicate" next to those in the next column.

Sub sbFindDuplicatesInColumn()
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
lastRow = Range("A65000").End(xlUp).Row
For iCntr = 1 To lastRow
If Cells(iCntr, 1) <> "" Then
matchFoundIndex = WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)
If iCntr <> matchFoundIndex Then
Cells(iCntr, 2) = "Duplicate" 
End If
End If
Next
End Sub 

Within this code, I changed Cells(iCntr, 2) = "Duplicate" to Cells(iCntr, 4) = Application.WorksheetFunction.Concatenate(Range(A1, A3)) to try to Concatenate the City and County together in a cell in column 4. However, every time I try to run the macro I get the "Can't execute code in break mode" error. I was hoping someone could help me figure out how to fix this and how to concatenate the data I need so I don't have to go through each entry by hand. Thanks!

Bonus question: Would it be possible to concatenate the city with county in the same cell? So it would go from this:

City - Municipality - County

to this:

"City - County" - Municipality - County

Thank you!

Answer 1

Try it like this:

Sub FindDuplicatesInColumnNew()

    Dim lastRow As Long

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    'Concatenate A and C in column D

    With Range("D2:D" & lastRow)

        .Formula = "=A2 & "" "" & C2"

        .Value = .Value

    End With

    'Find the duplicates and flag them

    With Range("E2:E" & lastRow)

        .Formula = "=IF(COUNTIF(D:D,D2)>1,""Duplicate"","""")"

        .Value = .Value

    End With

End Sub