Problem with Range using Auto Filter

[Opv]

I have a simple Auto Filter on column A to simply filter a sheet which contains information on multiple people. I have a row below the data rows with SUBTOTAL formulas so that once filtered I can see totals for the filtered individual.

My problem is that once I click on the drop down list and select an individual, the filter hides my subtotal row. How can I tell the filter to only filter a fixed range of rows, i.e., A4:A137 and at the same time expand downward as rows are inserted between the data rows and the subtotal row?

Name
A4 Tom
A5 Tom
A6 Tom
A7 John
A8 John
A9 John
A10 Alvin
A11 Alvin
A12 Alvin
...
A137 (blank) INSERT HERE
SUBTOTALS

[mikerickson]

Insert a blank row between row 137 and the subtotal row. Select only rows above 138 and then AutoFilter

or

Put the subtotal row at the top of the sheet and don't select it before setting AutoFilter

[p45cal]

A pivot table. Not a popular answer, I know, but it'll be the most flexible in the end. Elaborate on your example a bit.. what are the subtotals subtotals of? Perhaps add a bit more data?
If it's just a count of names then the following was achieved by putting [what I've called] 'Header' into both the row and data areas (or Row Labels and Values areas in xl2007), then removing the Grand Total row.
Attachment 2952
There are two other pics I want to show but I've reached the board's limit of one per message. I'll try and post them in another message or two..

[p45cal]

This is the result of not filtering:Attachment 2953

[p45cal]

And this shows what appears in xl2007 when you click the dropdown arrow (it's a bit simpler-looking in pre-xl2007)Attachment 2954

[Opv]

Thanks, mikerickson. I had tried the blank row already but I added it while the filter was in effect. I turned the filter off and assured the blank row was in place and then reset the filter and it is working now, in that it is showing the subtotal row.

That said, I am intrigued by p45cal's suggestion on the use of Pivot Tables. I used Pivot Tables years ago before I retired. It has been so long I'd probably have to learn the process from scratch again. Still, it might be worth the effort. My worksheet tracks daily hours, daily pay and several other miscellaneous columns of data for four or five of my brother's sons who help him on his farm from time to time. I consolidated all of his individual worksheets into one using the SUMPRODUCT function so that the individual rows reflect running totals for the appropriate person. My brother is interested in coming up with a way to track what he pays each person each week. The problem is the worksheet does not necessarily have all days of the week represented, as his sons do not work every day. They may go a day or two or as long as a month or longer without any work activity, so I've been reluctant to include a bunch of blank rows with zeros in them just to account for every day of the week for each person.

If I can't figure out the Pivot Table thing, I"ll post a sample of the worksheet for further assistance.

Keep your fingers crossed.

Thanks,

Opv

[Opv]

P.S. I am using Excel 2000.

[p45cal]

I can't speak for xl2000 since the earliest available to me is now xl2003 however, as you should be able to see in this pic, the dates are not continuous, nor are they in order, yet the table shows the results (counts in this case) with the data grouped into weeks (7 days). I've chosen to show names when there's no data present, but not show weeks when there's no data present.
(I've only recently been trying to get to grips with pivot tables myself and they do seem pretty powerful and flexible.)
Attachment 2955

[Opv]

On second thought, I doubt I'll pursue the Pivot Table function at this time, not for any reason other than I'm doing this for my brother and I know he is not going to want to have to learn the process. I think I'll stick with trying to find a solution that I can do with a formula so that he doesn't have to know why or how it works.

Thanks,

Opv