Microsoft Excel 2010 can highlight duplicate values using a preset conditional formatting command, but if you need to find the duplicates by using a macro, you can use Visual Basic for Applications (VBA), which is built into Microsoft Excel. While VBA doesn't have a function that will find duplicates, you can exploit the COUNTIF function to find out which cells hold duplicate information.
Open the Microsoft Excel 2010 file that contains the data you want to check for duplicates. When the spreadsheet comes up, press "Alt" and "F11" to load the VBA console.
Right-click on your worksheet on the left side of the VBA screen. Move your mouse over "Insert" and choose "Module." Double-click the new module that appears a few lines below the worksheet.
Place the cursor in the white space on the right side of the screen. Type in "Sub Dupecheck()" and press enter. VBA will automatically enter the "End Sub" command for you.
Place your cursor on the line between the "Sub" and "End Sub" lines. Enter the following lines of code:
Dim x As Long
Dim endrow As Long
endrow = Range("A50000").End(xlUp).Row
This code will create two variables, "endrow" and "x," and then establishes the value for "Endrow." This code will search through 50,000 lines in a single column, but if you need to so look through more lines, just increase 50,000 to whatever number you need. This code is also designed to look through column "A," but you can simply change "A" to whatever column letter you need.
Place your cursor on the next line and enter the following code:
For x = endrow To 1 Step -1
This is the heart of the code that will look through each cell and determine if an identical cell exists above it. If it finds a duplicate, it will turn the cell background a yellow colour. You can change every instance of "A" if you are working on a different column. You can also change the three numbers after "Interior.color" to change what colour the cell turns.
Click the "x" in the upper-right corner of the screen to close the VBA console. Click the "Developer" tab at the top of the screen. Find the "Macro" button on the left side of the ribbon and click it. Select "Dupecheck" from the list of available macros and click "Run." Excel will now highlight each duplicate in the column of data.
This dupe checker will only highlight the duplicate value, leaving the first value that appears in the column unmarked.
Tips and warnings
- This dupe checker will only highlight the duplicate value, leaving the first value that appears in the column unmarked.