Advanced Filter - Criteria Range containing blanks

Chazzo

New Member
Joined
Dec 28, 2014
Messages
15
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.

ABCDEFGHI
1Team ScoreDateTeamTeamScoreDate
2Team 16012/1/17Team 1
3Team 25012/1/17Team 2
4Team 38012/1/17
5Team 47012/1/17Team 5
6Team 59012/1/17
7Team 14012/2/17
8Team 26012/2/17
9Team 35012/2/17
10Team 42012/2/17
11Team 58012/2/17

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,883
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

New Member
Joined
Dec 28, 2014
Messages
15
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top