autofilter and subtotal not playing nice 1

[sarahnade]

I would like to count the number of cells still visible after an autofilter. I know the Excel SUBTOTAL function will do this. I must be doing something incorrectly because this isn't happening for me.

My code looks like this:

...
Range("Fund_table").AutoFilter Field:=2, Criteria1:=Id, VisibleDropDown:=False
            If Application.Evaluate("SUBTOTAL(3, FundId)") = 0 Then
...

Fund_table is a table imported from Access, Id is the integer I'm looking for, and FundId is a named range (a column within Fund_table). I get no errors from the code, but the result of the subtotal is the total number of rows, and not just the visible ones. Either I'm not applying the autofilter correctly or subtotal is counting more than it should.

Any help would be appreciated.

Sarah
-don't panic, I'm mostly harmless-

 

[SkipVought]

Please explain, functionally, WHAT you want to do, rather than HOW your think it ought to be done.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, who was the first to sarahnade you? And was it a good preformance? ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Trying to devine what your purpose might be.

1. I'd has ONE table that has BOTH Fund and IndexID data. Depending on the structure, that may not be feasible.

2. I'd designate certain cells as criteria for the table AutoFilter. One of thos criteria, using the One Table approch, would be a "list" containing Fund & IndexID, to select in a cell with Data > Validation -- LIST

3. Either use the Worksheet_Change event or a BUTTON click event to fire the procedure that sets the table AutoFilter criteria.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sarahnade]

For this section, a simple "COUNTIF" will work just fine.

I was only trying to use autofilter to make my life easy for the rest of the code. But, I need to call from a cell, and I can't do autofilter that way.

So, this part of the code is fine. Now I just need to work on the rest of the code.

My question is answered. I'm on my own now. If I have more questions, I'll be sure and post them.

Thanks again for your help!

Sarah
-don't panic, I'm mostly harmless-