Return filtered tasks

[Finius Eetch]

I'm having a problem getting the correct number of rows returned to me after
filtering.


I have the following lines in my Init Function.

ActiveSheet.AutoFilterMode = False
Range("A1", Cells(Rows.Count, 1).End(xlUp)).Resize(, 13).Select
total_nachos = Selection.Rows.Count

this returns the proper value to total_nachos, which is how many rows are in
my entire excel sheet a little over 20,000.

I dynamically build my filter list off column I (9) and need to store the
number of filtered tasks found when applying the filter. Here are the lines
I'm using when I apply the filter.

Selection.AutoFilter Field:=MyCategoryCol, Criteria1:=CatBuffer(2)
This applies the filter I want, and I see in the lower left the accurate
number of tasks. However, I cannot seem to get an accurate count returned to
the code - which is what I need, I get numbers that are way out of wack when
I populate my value.

Range("I1", Cells(Rows.Count, 1).End(xlUp)).Resize(, 13).Select
value=Select.Rows.Count

I'm sure it's something simple, I'm more of a VBA MS Project guy and this is
my first excel macro which has been a booming success with all my Project
knowledge, but the functions for returning ActiveSelections of rows/tasks are
different.

 

[Ron de Bruin]

Hi Finius

See
http://www.contextures.com/xlautofilter02.html#Count

 

[Finius Eetch]

Ron,
What i'm looking for is the ability to hold the 'number of records
found' in a VBA variable. This variable is passed into various VBA functions
that crunches all my data. Is there a way to extract that information that I
haven't found? Something along the lines of.

dim total_records_found as long
total_records_found =FilteredTaskRecords

I've tried Selection.Rows.Count, but the numbers it gives me are wrong.
I'm not very familiar with the Range command so perhaps that is why my info
from Selection.Rows.Count is wrong. I need to count the number of records in
the I column, or Column 9 after I've applied a filter to Column I/9. I do
not want to count each cell in a loop if possible. Obviously excel has the
answer for me when I sort 23,000 rows of categories for 'Kids' and it returns
213 entries. However, I get back on the order of 14,000 and change. The
ONLY time I get the right data is before any filters are applied and I get
the total number of rows.

Any help would be greatly appreciated. Once I get this knocked out I can go
back to my MSProject hole
'F.E.

 

[Ron de Bruin]

You can use this with code

total_records_found = application.WorksheetFunction.Subtotal(......................)

 

[Finius Eetch]

Ron,
Thanks, that gave me exactly what I needed to extract the number of
records.