+ Reply to Thread
Results 1 to 8 of 8

Different results for Auto Filter and Advanced Filter

  1. #1
    Registered User
    Join Date
    12-01-2016
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    2

    Different results for Auto Filter and Advanced Filter

    I have an export (30k+ records) from a database that I try to filter with Advanced Filter.
    With Auto filter I see 3 records, but none with Advanced Filter.
    Please see the attached file.

    What am I missing? Is this a bug in Excel 2007?

    Thank you in advance!

    Student
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Different results for Auto Filter and Advanced Filter

    I could be mistaken, but I have a feeling I read somewhere that AF has a limit to the number of rows it can handle, I dont remember the limit though
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,153

    Re: Different results for Auto Filter and Advanced Filter

    1) In G3, you have =+G5, means "=2000521193" as String. It should be simply 2000521193, or =200052119 or even =A5.

    2) You have Criteria A2:AL3, but it should be only G2:G3, otherwise it treats all creteria with And operator, so the data needs to be all blanks except Col.G.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Different results for Auto Filter and Advanced Filter

    @ jindon, I tried with removing the =, didnt change anything. Did not adjust the criteria range though

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,153

    Re: Different results for Auto Filter and Advanced Filter

    See the attached
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Different results for Auto Filter and Advanced Filter

    @ jindon, I was not doubting you, it didnt change anything because I had not adjusted the range lol

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Different results for Auto Filter and Advanced Filter

    Hi,

    Your code is correct. The issue is just that D3 is not actually empty. If you select D3 and press Delete and then run your existing code, it will filter the same way as the Autofilter.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  8. #8
    Registered User
    Join Date
    12-01-2016
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    2

    Re: Different results for Auto Filter and Advanced Filter

    Thanks. I will use Auto-Filter for future.

+ 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. [SOLVED] advanced auto filter vs. regular filter
    By ammartino44 in forum Excel General
    Replies: 1
    Last Post: 08-28-2015, 05:07 PM
  2. [SOLVED] Advanced filter not equal (<>) results
    By whocares4 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2015, 01:15 PM
  3. Copy only filtered results of advanced filter
    By csh8428 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2013, 04:51 PM
  4. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  5. Delete Rows hidden by either Auto Filter and Advanced Filter
    By lesoies in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2013, 10:29 AM
  6. [SOLVED] Advanced Filter results don't match auto filter
    By WaterWings in forum Excel General
    Replies: 1
    Last Post: 09-13-2012, 05:37 PM
  7. refresh advanced filter results
    By eagle in forum Excel General
    Replies: 2
    Last Post: 11-18-2005, 05:20 AM

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