The macros on this webpage assume that your data is in a normal range.
If your data is in a List (in xl2003, Data>List) or Table (in xl2007-2013, Insert Tab>Tables Group>Table)
see the macro examples on this page :VBA code examples for a List or Table in Excel 2003-2013
Or if you use Tables in Excel 2007-2013 try this add-in :Table Tools Add-in for Excel 2007-2013
Important if your data is in a normal range:
1) Use headers in the first row of your data (1 Header row)
2) Don’t use empty or hidden rows in your data
3) Don’t use merged cells in your data
4) Turn AutoFilter or Advanced Filter off before you run the code
Do you wonder why I only use AdvancedFilter in the code to create the unique list and not use xlFilterCopy to filter and copy the data to a new sheet or workbook. The reason why I use AutoFilter for that in the code is that there is a bug in AdvancedFilter xlFilterCopy. It will not copy the correct data when you have duplicate headers in the first row of your data. Now with 16384 columns in Excel 2007-2013 the chance that this will happen is much bigger.
Copy the code in a Standard module of your workbook, if you just
started with VBA see this page.
Where do I paste the code that I find on the internet
Try the code on the pages below or Download the Example workbook
You can download a example workbook here with all the code you find on
the pages above.
Download Example workbook