How to find duplicate entries across columns using excel VBA
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:
2 thoughts on “How to find duplicate entries across columns using excel VBA”