AdvancedFilter with no criteria

[opopanax666]

Hi guys,

(Edit : made it a bit simpler)

When using AdvancedFilter without criteria, AF returns all values in the range. How do I stop this from happening, i.e. no criteria should not return any values.

Thanks for any feedback

[Loz]

If you prefer to return no rows when all the criteria are blank, one method is to set-up an additional criterion that tests for all blank criteria and returns a value that will force the filter to display no rows.

Pick a field name to duplicate and add it to the end of your current criteria row. Then set-up something like the following formula under the duplicated field name....

=IF(MAX(LEN(A2:B2))=0,NA(),A2)

This must be entered as an array formula (using Ctrl Shift and Enter so that it has {} braces around it).

You should change A2:B2 to wherever the current criteria values are, and A2 should be the criteria value of the field we are duplicating.

You will have to adjust the criteria range in the AF dialog box to include the new duplicated field.

The formula will return the #NA error value when all the other criteria values are blank and all the rows will be filtered out (assuming there are no #NA values in the chosen duplicated field - hopefully unlikely). Otherwise it will simply duplicate the criteria entered in the chosen field and the filter should work as normal.

If you have criteria on multiple rows, you can copy the formula to these rows as well.