AutoFilter (2003)

Topic

New Reply

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.

• As presented the Name criteria offers none of the four names as an option in the pull-down list
• Adding text in cell E2 will add Sandra to the pull-down list in the Name criteria
• 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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

It is such a pleasure dealing with such capable mentors.

Reply | Quote

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

Reply | Quote