Forum Discussion
Write a custom filter
You can create complex criteria by leaving the top cell of the criteria range (that ordinarily contains the field name/column header) blank, and entering a formula in the cell below it that returns TRUE for the values that you want to remain visible and FALSE for the others.
The formula in E2 is =COUNTIF($E$5:$E$21,$E5)>1. This returns TRUE if the first data cell E2 is a duplicate.
Result: only duplicate values are shown.
If you want to see each value that has duplicates only once, tick the check box 'Unique records only'.
If I change the formula in E2 to =COUNTIF($E$5:$E$21,$E5)=1 and reapply the filter, only unique values remain:
What about passing any VBA function as the criteria - a named function I have written in my VBA editor - instead of this sort of Excel-style function? Is that possible with the VBA AdvancedFilter function or should I just write my own filter manually by using the Row.Hidden = True property?
You can use a VBA function in the formula in cell E2 in my example, as long as it returns TRUE or FALSE for the first cell of the source range. You can use the criteria range in the WorksheetFunction.AdvancedFilter function too.
If that doesn't suit your needs, you can create your own filter code of course, but it will probably be slower.