Autofilter overshooting range

mfleuette

New Member
Joined
Sep 18, 2002
Messages
27
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,217,346
Messages
6,136,043
Members
449,981
Latest member
kjd513

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top