Autofilter not selecting all qualified cells

[jim may]

Here's originally an small unfiltered table

Excel Workbook
	A	B	C	D	E
1
2
3
4
5	Cout Seq	Amt	Name	Going?	Date
6	1	123.00	Howard	Y	7/15/2003
7	2	234.00	Bruce	N	7/28/2003
8	3	345.00	Ron	N	7/21/2003
9	4	456.00	Howard	N	8/1/2003
10	5	567.00	Ron	Y	7/25/2003
11	6	311.00	Bruce	Y	8/15/2003
12	7	911.00	Henry	Y	8/20/2003
13	8	678.00	Joe	N	6/30/2003
14	9	347.00	Howard	Y	7/29/2003
15	10	456.00	Ron	Y	7/31/2003
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A6	=SUBTOTAL(3,C$6:C6)
A7	=SUBTOTAL(3,C$6:C7)
A8	=SUBTOTAL(3,C$6:C8)
A9	=SUBTOTAL(3,C$6:C9)
A10	=SUBTOTAL(3,C$6:C10)
A11	=SUBTOTAL(3,C$6:C11)
A12	=SUBTOTAL(3,C$6:C12)
A13	=SUBTOTAL(3,C$6:C13)
A14	=SUBTOTAL(3,C$6:C14)
A15	=SUBTOTAL(3,C$6:C15)

Then I filter on Column D - Going? for Y

Excel Workbook
	A	B	C	D	E
1
2
3
4
5	Cout Seq	Amt	Name	Going?	Date
6	1	123.00	Howard	Y	7/15/2003
10	2	567.00	Ron	Y	7/25/2003
11	3	311.00	Bruce	Y	8/15/2003
12	4	911.00	Henry	Y	8/20/2003
14	5	347.00	Howard	Y	7/29/2003
15	6	456.00	Ron	Y	7/31/2003
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A6	=SUBTOTAL(3,C$6:C6)
A10	=SUBTOTAL(3,C$6:C10)
A11	=SUBTOTAL(3,C$6:C11)
A12	=SUBTOTAL(3,C$6:C12)
A14	=SUBTOTAL(3,C$6:C14)
A15	=SUBTOTAL(3,C$6:C15)

the Row headers of rows 6,10,11,12,14 are BLUE, but Row header 15 is Not, meaning the normal black like the other rows below..

In the immedaite window using
? activesheet.autofilter.range.address I get >>> $A$5:$E$15

So why isn't the row header of row 15 have the Blue color..

I had this same situation happen earlier this week on my xl2007, and the results were much MORE serious -- in a 4,000 total filtered records, only the first 3,000 row headers were Blue (and Correct), but the remaining 1,000 included values that qualified to be in the filtered list and those that did not qualify.

Confused.. Has any one else experienced this?

Jim

 

[Nalani]

Jim,

I laso tried on 2007 with your data and got the same results.

You may have tried this already or cannot apply it. But I changed the formula to: =COUNT($B$6:B6) and copied down. Sinces COUNT counts the cells in a range that contains numbers.

I got the Blue row header for row 15.

I don't have an explanation for the SUBTOTAL formula.

I'm assuming that B:C are not empty.