Excel - AutoFilter Misses Bottom Row

[FletchUK]

Hi All,

A colleague has a problem in Excel XP which has completely stumped me! When she applies AutoFilter to a list the filter is not applied to the bottom row of data. We've formatted the row so it's identical to the ones above - no luck. We have even copied a row from above and pasted the data into the bottom row - still no luck. We've tried highlighting the whole spreadsheet and then applying the AutoFilter - again, no luck.
The bottom row doesn't seem to be included in the AutoFilter range as when the data is filtered the row number does not turn blue and the row is visible whether the row meets the criteria or not. If we go into Advanced Filter the default range always stops one row above the final row.
Anyone know how this happens and how to correct it?
Thanks.

 

[xlbo]

How are you setting the range for the autofilter ?? Are you selecting a range or selecting entire columns ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FletchUK]

Thanks, your reply has led to some interesting experiments...
- If we select the entire range a1:bk285 the final row (285) is not filtered and appears all the time regardless of the filter criteria.
- If we do not select anything and simply apply the AutoFilter the same thing happens as above.
- If we click on ROW 1 to highlight the whole row the same thing happens.
But...
- If we select one or more COLUMNS (by clicking on them to highlight the full column) the AutoFilter DOES include the bottom row!
Do you know why is this? I have never come accross this before. Normally, if we click on the row where we want the filters to appear it includes all rows below until it finds an empty row.

 

[xlbo]

Are there any gaps in the data ie does row 284 have any data in it ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FletchUK]

Sorted it! My colleague had entered a subtotal in cell BK285 (the bottom row) to calculate the cells in that row. I changed this from subtotal to sum and now it works as expected.
Thanks for your help xlbo, much appreciated.