How to check for duplicates in Excel Workbooks using VBA?

Step #1: Identify the Excel Range

First off, open your Microsoft Excel spreadsheet and identify the cell range in which you would like to check for duplicates. Your range could be across single or multiple spreadsheet columns.

Step #2: Write your VBA Code

Next, in your open Excel workbook, hit ALT+F11 or hit the Developer button and then hit Visual Basic. Next, using the VB Project Explorer on the left side pane, insert a new VBA module in your Workbook and enter the following Subroutine which will check and highlight duplicated entries in Column A of your workbook. To improve the user experience, you might as well prompt the user to define a custom cell range during program runtime.

Sub Check_Duplicated_Values()
    Dim MyRng As Range
    Dim MyCell As Range
    'Check duplicated values in column A
    Set MyRng = Range("A:A") 
    For Each MyCell In MyRng
        If WorksheetFunction.CountIf(MyRng, MyCell.Value) > 1 Then
           'Color all duplicated cells
            MyCell.Interior.Color = RGB(0, 255, 255)
        End If
    Next MyCell
End Sub

Save your Code, and then Save your Excel file as a Macro enabled Workbook (.xslm file).

Note: If you don’t see the Developer menu in your Ribbon, you should enable it first.

Step #3: Run the Code and check Errors

From the Visual Basic editor run your code. Then, navigate to the spreadsheet, and note that the duplicated values in Column A will be highlighted as show in the simple example below:


When running your code, Excel might alert you that VBA Macros are disabled in your worksheet. If that is the case, you might want to adjust your Security settings to allow running Macros in your workbook. Always remember to carefully check any VBA code before running it in your computer.