Excel Duplicate Finder

After experiencing issues with some of my spreadsheets being rejected upon database importing due to duplicates, i decided to create a macro to make the solution simpler. The macro that i created, is contained within the excel file "excel_dupes_range_Value.xls" and can be easily applied to any spreadsheet you are working on.

Instructions:

1. Simply download this file, and open it in excel. Make sure to allow macros in the security dialog. In order to reduce confusion, i hid this workbook, so don't be alarmed when nothing new pops open - you will be able to tell if it is open in the next step.

2. The macro searches for duplicates only within the selection of cells. Select a group of cells (THIS IS CURRENTLY LIMITED TO DATA WITHIN THE SAME COLUMN).

3. In the toolbar go to Tools>Macro>Macros and you should see the "excel_dupes_range_Value.xls!findDupes" macro in the list of open workbooks. Select this and click "Run" -see example-

4. Once the macro is run, a new window will open up which displays all of the duplicate values. By double clicking on any value in the list, you will be taken to that value in the selection of data that you ran the macro on. If you click on the same value twice consecutively, it will take you to the second duplicate and so on. -see example-

Here is the duplicate findinding spreadsheet

-excel_dupes_range_Value.xls-

---
The macro works by identifying the selection's range, copying the data within the range into th macro spreadsheet, sorting the data, and then looking for two identical values in sequence. A loop works through one cell at a time comparing the present and previous value. The loop runs until it reaches the end of the range selected by the user's selection. Once duplicates are found, the values are added to a listbox, and then the search is performed based on that value in the originating spreadsheet.

Excel Macro Confirm MSG Box

I recently realized a danger in keyboard-shortcut driven macros. Excel allows you to tie a macro to common shortcuts (like CTRL-C, CTRL-B) and if pressed accidentally, changes made by the macro can not be undone. As a result i came up with this simple bit of code to force confirmation before the macro is run, through a MSG Box.



Simply place this at the beginning of each of your macros:
----

ans = MsgBox("Run Macro?", vbYesNo)
If ans = vbNo Then
Exit Sub
End If

----
If the user says "NO" the macro sub will exit, and if yes, the code below this section will be run. An easy way to protect users from accidentally misusing your macros.