Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    AutoFilter (2003)

    I may be the only person that has been bothered by AutoFilter being compromised by blank cells, but I have found that occasionally the filter results are not what I expected. The attached file can be used to demonstrate this situation.
    <UL><LI>As presented the Name criteria offers none of the four names as an option in the pull-down list
    <LI>Adding text in cell E2 will add Sandra to the pull-down list in the Name criteria
    <LI>Adding text at cell F15 will add the remaining 3 people to the list.[/list]If filtering a range where there may be empty rows, it is recommended that immediately to the right of the range of interest an additional column be included in the filter and filled with any character.
    Attached Files Attached Files
    Regards
    Don

  2. #2
    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 (2003)

    It is not "blank cells", it is blank rows and/or blank cols which will "compromise" the determination of your range. Excel judges the "Current region" which is bounded by blank rows/columns.

    Adding a complete column of some sort will help, but an alternative way (if you must have non-contiguous areas) is to explicitly select the range you want to filter on before selecting the autofilter, since excel's "implicit guess" is based on the current region.

    [This is not just the method for Autofilter, it is also the method for sorting and any other instance where excel must guess the range based on only one cell selected.]

    Steve

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoFilter (2003)

    Steve said it faster than I could, but he's exactly correct: if you select columns A through D and then turn on AutoFilter, everything works. However, I am boggled by the E2 & F15 stufff: I would have expected no change. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: AutoFilter (2003)

    1) When you select a cell, then activate AutoFilter, Excel will use the "current region" of the cell as "database". The current region is the smallest rectangular area containing the active cell that doesn't have a blank row or column. Since row 2 is blank, the current region of (for example A1) consists of some cells in row 1 only. As soon as you enter a value in an adjacent cell in row 2, the current region suddenly expands to include more rows.
    2) If you select an entire range, then activate AutoFilter, Excel will try to use the entire selection as "database" even if there are blank rows or columns within the selection.
    3) Excel's filtering options (AutoFilter and Advanced Filter) expect a database-like table in which information is present in all rows, even if this means repeating the same data. In your sample worksheet. the name "Sandra" only applies to row 3, *not* to rows 4 through 31. If rows 4 through 31 "belong" to Sandra, you should fill down the name Sandra from A3 to A31, then delete row 3.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank You All

    It is such a pleasure dealing with such capable mentors.
    Regards
    Don

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter (2003)

    Since you are on Excel 2003, try to exploit the Data|List|Create List functionality.
    Microsoft MVP - Excel

Posting Permissions

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