In this article we will look at how to find duplicate entries across columns using Excel VBA. Consider you have 3 columns of data with few duplicate rows as seen below:

And you need to highlight the rows that are not unique. As there is no direct way to achieve this, we need to loop through all the rows in the data. For each row, you need to check if there is another row with the exact same data. For this, we will be using two for loops – first one to loop through all the rows and second to find a match for the current row. The comments in the code below will help you follow through the code.

Sub highlightDuplicateRows()

    Dim lastRow As Integer, compRow As Integer, rowNo As Integer

    'Get the last row of data
    lastRow = Sheet1.Range("A1").CurrentRegion.Rows.Count

    'Loop through all the rows
    For rowNo = 2 To lastRow

        'For each rowNo, loop through all the remaining rows
        For compRow = rowNo + 1 To lastRow

            'Check if a match is found in Column A for the current rowNo
            If Range("A" & compRow) = Range("A" & rowNo) Then

                'If a match is found in Column A, check correspoding values of column B
                If Range("B" & compRow) = Range("B" & rowNo) Then

                    'If a match is found in Columns A and B, check correspoding values of column C
                    If Range("C" & compRow) = Range("C" & rowNo) Then

                        'Duplicate data accross 3 columns found. Highlight both the rows
                        Range("A" & compRow & ":C" & compRow).Interior.Color = vbYellow
                        Range("A" & rowNo & ":C" & rowNo).Interior.Color = vbYellow
                    End If
                End If
            End If
        Next compRow
    Next rowNo
End Sub

As you can see, first column A of a row is compared with column A of each remaining rows. If there is a match, the next column is checked. This is continued till the last column. Thus, you can easily adapt the code for any number of columns. Here is how the output will look like

If you want to remove the duplicate rows, you need to change the code a bit. For deleting rows, you should always start from the last row and move upwards.  This is because the row counter will get messed up after deleting a row when you start from the top.

Sub deleteDuplicateRows()

    Dim lastRow As Integer, compRow As Integer, rowNo As Integer

    'Get the last row of data
    lastRow = Sheet1.Range("A1").CurrentRegion.Rows.Count

    'Loop through all the rows
    For rowNo = lastRow To 2 Step -1

        'For each rowNo, loop through all the remaining rows
        For compRow = rowNo - 1 To 2 Step -1

            'Check if a match is found in Column A for the current rowNo
            If Range("A" & compRow) = Range("A" & rowNo) Then

                'If a match is found in Column A, check correspoding values of column B
                If Range("B" & compRow) = Range("B" & rowNo) Then

                    'If a match is found in Columns A and B, check correspoding values of column C
                    If Range("C" & compRow) = Range("C" & rowNo) Then

                        'Duplicate data accross 3 columns found. Delete the current rowNo and proceed to next row
                        Range("A" & rowNo).EntireRow.Delete
                    End If
                End If
            End If
        Next compRow
    Next rowNo
End Sub

Unlike example 1, in example 2, we will delete only the rows from the outer for loop counter, that is, starting from the bottom. So, the topmost unique row will remain as seen below:

Read also: What is VBA Range.Cells and How Do You Use It?