# Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter

1. ## Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter

I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.

But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter.
Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.

I've tried changing the Target Row to >2 but that didn't work.

Any idea how to make the criteria range bigger, and no problems of breakage if you clear the cells?
It makes for a very useful automated Advanced Filter, any advice much appreciated.

Here's the code :

``Please Login or Register  to view this content.``
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...

2. ## Re: Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter

It would probably help if you posted example file and detailed criteria that was working and one that was not.

Also I think part of the problem maybe your confusion over what Target actually is.

3. ## Re: Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter

Ok here they are, one broken , one working,

4. ## Re: Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter

Code change

``Please Login or Register  to view this content.``
You also need to alter the formula on DATA sheet to handle single row entry.

First formula is an Array formula so commit using CTRL+SHIFT+ENTER
DATA!AX3: =SUM(IF(Team_Filter!A3:D3="",0,1))

DATA!AY3: =IF(AX3=0,"","<>")

DATA!AZ3: =IF(ISBLANK(Team_Filter!A3),\$AY3,Team_Filter!A3)

You can drag that across BA3:BC3

5. ## Re: Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter

Ok that's great, works brilliantly , Thankyou

6. ## Re: Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter

Originally Posted by Andy Pope
Code change

``Please Login or Register  to view this content.``
You also need to alter the formula on DATA sheet to handle single row entry.

First formula is an Array formula so commit using CTRL+SHIFT+ENTER
DATA!AX3: =SUM(IF(Team_Filter!A3:D3="",0,1))

DATA!AY3: =IF(AX3=0,"","<>")

DATA!AZ3: =IF(ISBLANK(Team_Filter!A3),\$AY3,Team_Filter!A3)

You can drag that across BA3:BC3

Hi Andy!

If you have time, would you mind looking at my code?

https://www.excelforum.com/excel-pro...ml#post5528073

There are currently 1 users browsing this thread. (0 members and 1 guests)