Autofilter overshooting range

[mfleuette]

I have searched through the Excel message board, but was unable to find a similar post, so I will post this thread and hope some kind-hearted soul can help.

I have a large spreadsheet (150+ columns, 1000 rows)

The rough format is:

headers (rows 1-7)

detail data (rows 8-990)

sums & analysis (rows 991-1000)

PROBLEM: Auto-filters are on rows 7-990; however, when I look in the autofilter for a given column, values from the totals appear in the LOV; more importantly, when I apply an auto filter, the first 4 lines of the sums & analysis (i.e. rows 991-994) disappear.

REQUEST: any help in configuring the auto-filters properly so that totals do not disappear.

As always, any help is greatly appreciated!

 

[Yard]

Try:

Remove the filters.

Ensure there is at least one blank row between the last data row and the first row of the sums & analysis section.

Select the range you want to filter, including the column headers and excluding the blank row and the sums & analysis area at the bottom.

Reapply the filter.

 

[mfleuette]

Many thanks - problem solved!

 

[peejay]

I have this problem too, but this solution doesn't seem to work.

I have a number of columns of data fields, with a number of rows of data, then a blank row (or 2) then some sub totals. With the auto-filter turned off, I select the column header and values I want to filter within one column (excluding the cells in the blank rows and sub totals), re-apply the autofilter but still find the subtotal rows being included in the filter, which is frustrating the heck out of me.

Is there something anyone else could recommend?

Many thx.

 

[Yard]

Hi,

Are you *sure* the blank rows are blank?

You need to remove the autofilter, not just set it to Show All.

You need to have the "filter area" selected when you re-create the filter.

Does that work?