Auto-filter includes too much range

[Atreides]

Hi

I select a range (e.g. A6:Z267) and then apply the auto-filter. However once
the filter is applied it has picked up down to row 269 - i.e. rows below
where it should have stopped. How can I fix this?

Thanks

Atreides

 

[Dave Peterson]

Insert a completely empty row between your data that should be filtered and that
last couple of rows.

 

[Atreides]

Hi Dave,

Excel is strange sometimes. There was already a completely empty row between
my data and that last couple of rows (the totals). And also I explicitly
selected the range I wanted before using auto-filter.

However, I inserted a couple more empty rows between the data and the totals
and it worked then. The strange thing is that I then deleted those extra rows
(so it's now looking identical to how it was in the first place) and yet
auto-filter works properly now (even if I turn it off and apply it again!)

Thanks, that was really bugging me

- Atreides

 

[Dave Peterson]

I've had similar problems with autofilter (I don't recall the details, though).

If you're putting =subtotal() in that bottommost row (excluded from the
autofilter range), you may want to put them at the top of the data--above the
headers.

If you freeze panes, they're always visible and could be even more useful at the
top.

 

[Atreides]

Hi Dave,

Thanks again for your help,

Yes I have subtotals at the top as well as at the bottom (for readability).
But the problem was much greater - it wasn't just erroneously taking in 1 row
of totals but the 8 rows below them (again separated by a blank row) that had
the Legend for the spreadsheet!

- Atreides