+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    01-16-2014
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    5

    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...
    Last edited by PaulGW; 05-01-2014 at 11:06 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    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.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    01-16-2014
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    Ok here they are, one broken , one working,

    Thanks for your help

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    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. #5
    Registered User
    Join Date
    01-16-2014
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    5

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

    Ok that's great, works brilliantly , Thankyou

  6. #6
    Registered User
    Join Date
    04-14-2021
    Location
    Long Island, NY
    MS-Off Ver
    Office 365
    Posts
    37

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

    Quote Originally Posted by Andy Pope View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Advanced filter with function as criteria range?
    By dakke in forum Excel General
    Replies: 23
    Last Post: 06-10-2021, 04:48 PM
  2. Advanced Filter : One Criteria Cell Breaks the Filter - Returns Only Headers
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2014, 10:06 AM
  3. Advanced Filter Criteria Range Help
    By JoeGio25 in forum Excel General
    Replies: 0
    Last Post: 10-23-2011, 07:34 PM
  4. Advanced Filter - Criteria Range Issue
    By EB78 in forum Excel General
    Replies: 0
    Last Post: 06-27-2008, 05:38 PM
  5. Advanced filter and Criteria Range
    By gearoid in forum Excel General
    Replies: 2
    Last Post: 07-20-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1