Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoFilter Failure (2003)

    I am experiencing an odd failure with a worksheet trying to use the AutoFilter feature. I have 117 records. I am trying to filter the view of the records based on an "X" value for one of the columns for each of the records. However, the AutoFilter is returning "X" and some of the null value records. I have highlighted those null value records and pressed delete to ensure the cell is clear, but AutoFilter is still returning those records for view. Sort Ascending and Sort Descending is not helping either. AutoFiltering on Non-Blank or even Blank doesn't seem to return the proper records either.

    What might be inhibiting this?

    Thanks
    Amy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AutoFilter Failure (2003)

    Could you attach (a sanitized copy of) the workbook to a reply?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter Failure (2003)

    Sorry about that. Here it is.

    Amy
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter Failure (2003)

    <P ID="edit" class=small>(Edited by mbarron on 25-Aug-08 13:48. added explanation)</P>I don't have an explanation as to what causes the hiccup, but it can be cured by removing row 42 - it contains no data.

    Actually I do have an explanation. Since row 42 was blank, Excel assumes that is the end of your table and only filters from rows 2 through 41

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter Failure (2003)

    The other option, other than deleting row 42, is to select the entire table first and then "turn on" the Auto Filter.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter Failure (2003)

    Thanks Mike. Both worked. Bizarre, but I guess the row 42 essentially splits the table causing AutoFilter to be confused.

    Amy

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AutoFilter Failure (2003)

    I wouldn't say Autofilter was "confused". Unless you specifically indicate the range, it presume that the filter should be based on the current region. This is the area bounded by an empty column and and an empty row.

    Most builtin features of XL handle an unspecified range in this manner.

    Steve

Posting Permissions

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