+ Reply to Thread
Results 1 to 6 of 6

Advanced filter returning all dates instead of those in criteria range, or error

  1. #1
    Registered User
    Join Date
    01-28-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    3

    Question Advanced filter returning all dates instead of those in criteria range, or error

    Hi all,

    I trying to use advanced filter to copy rows with dates under "DateCreated" that are within the criteria range.

    I made a test workbook where this worked fine, however when I run the filter, one of two things happens:
    1) using the table name of the extract data causes the "extract range contains missing or illegal references" error message
    2) using a range within the table returns all rows within that range, and not just the ones within the criteria range. A text criteria (ex. "Donna") would run correctly.

    I though problem 2) was happening because of the date cell formats, which is yyyy/mm/dd h:mm, but changing the criteria to the same format hasn't helped.

    Problem 1) maybe has to do with the table being linked? But even after removing the link, the same message appears.

    I haven't created a macro or VBA code for the actual workbook yet, I'm just manually applying the advanced filter while trying to get it to work. Unfortunately, I can't post the actual workbook.

    There's no repeat headers, though there are multiple columns with date information in the extract table.

    Any ideas what might be causing either problem?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,841

    Re: Advanced filter returning all dates instead of those in criteria range, or error

    Difficult to give advice without seeing what you can see
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-28-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    3

    Re: Advanced filter returning all dates instead of those in criteria range, or error

    Sorry about that. I can post the test workbook to give an idea of what I'm trying to do. The actual workbook contains sensitive info.
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,841

    Re: Advanced filter returning all dates instead of those in criteria range, or error

    OK, you can put, in A2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and this in B2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or you could put the dates in, say D1 and E1 and then use in A2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and B2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In the cells, you will see: >=42186 and <=42324

    So, you could actually put your Advanced Filter Criteria on a separate, hidden (maybe) worksheet, and just enter your dates and monitor those (date) cells in your Worksheet Change event handler.


    Regards, TMS

  5. #5
    Registered User
    Join Date
    01-28-2016
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    3

    Re: Advanced filter returning all dates instead of those in criteria range, or error

    Your formulas worked, TMS. Thank you!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,841

    Re: Advanced filter returning all dates instead of those in criteria range, or error

    You're welcome. Thanks for the rep.

+ 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. [SOLVED] Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2021, 04:30 PM
  3. Advanced filter not returning range with numeric format below 7 characters
    By morsoe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2014, 03:39 AM
  4. Advanced Filter Criteria Range Help
    By JoeGio25 in forum Excel General
    Replies: 0
    Last Post: 10-23-2011, 07:34 PM
  5. Setting criteria between two dates (advanced filter)
    By Chrilliams in forum Excel General
    Replies: 0
    Last Post: 02-08-2011, 12:07 AM
  6. Advanced Filter - Criteria Range Issue
    By EB78 in forum Excel General
    Replies: 0
    Last Post: 06-27-2008, 05:38 PM
  7. 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