 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
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
Use AutoFilter to filter and copy the results to a new worksheet
Use AutoFilter to filter and copy the results to a existing worksheet
Create a new workbook for every unique value
Create a new sheet for all Unique values
Create a new sheet for all Unique values or paste it below the existing data if the sheet exists
You can download a example workbook here with all the code you find on
the pages above.
Download Example workbook