Advanced Filter - Criteria Range containing blanks

[Chazzo]

Hello Everyone!

Is there a way to deal with blank cells in a criteria range when using the Advanced Filter?

In the illustration below, Column A:C is the 'List Range', Column E will contain data for the 'Criteria Range' and column G1:I1 is the 'Copy To' destination.

When using the Advanced filter and selecting E1:E3 with no blanks, I get the desired results in the Copy To destination.

However, if the criteria range is extended down selecting E1:E5 (to include 'Team 5') if the range contains a blank, the filter returns all of the data in the list range vs. Team 1, 2, & 5.

Thanks in advance for your feedback.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team [/TD]
[TD]Score[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Team[/TD]
[TD][/TD]
[TD]Team[/TD]
[TD]Score[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Team 1[/TD]
[TD]60[/TD]
[TD]12/1/17[/TD]
[TD][/TD]
[TD]Team 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Team 2[/TD]
[TD]50[/TD]
[TD]12/1/17[/TD]
[TD][/TD]
[TD]Team 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Team 3[/TD]
[TD]80[/TD]
[TD]12/1/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Team 4[/TD]
[TD]70[/TD]
[TD]12/1/17[/TD]
[TD][/TD]
[TD]Team 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Team 5[/TD]
[TD]90[/TD]
[TD]12/1/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Team 1[/TD]
[TD]40[/TD]
[TD]12/2/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Team 2[/TD]
[TD]60[/TD]
[TD]12/2/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Team 3[/TD]
[TD]50[/TD]
[TD]12/2/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Team 4[/TD]
[TD]20[/TD]
[TD]12/2/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Team 5[/TD]
[TD]80[/TD]
[TD]12/2/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Hi

There are several solutions, this is just one.

You do not explain why there are holes in the list of the criteria values.

Assuming that you want to filter using different lists of criteria values, I'd have the list of values somewhere else and in the criteria range just a formula that checks if the value exists in the list.

This is an example, working in Sheet1:

in E1: "Allow"
in E2: =match(A2,CriteriaValues,0)

In K2:K10 enter some values for the criteria (with blanks, if you want).

Define the range CriteriaValues as

=Sheet1!$K$2:$K$10

Invoke the advanced filter and use Sheet1!$E$1:$E$2 for the Criteria.

Each time you change the values in the criteria list just execute the filter again.

 

[Chazzo]

pgc01,

Thank you for the solution, this will certainly work!

I plan to create an input range for a user to query from a much larger data set and my thought is users will not always populate the input range from top to bottom. That should explain why there may be holes in the list of criteria values.

I will add VBA code to a button to invoke the advanced filter for the user and this should work great.

Thanks again, hope to pay it back soon.

-Chazzo

 

[pgc01]

I'm glad it helped.