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!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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.
 

peejay

Board Regular
Joined
Jul 11, 2003
Messages
79
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

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,818
Messages
5,598,280
Members
414,223
Latest member
Accountant2B

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
Top