[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Return filtered tasks

Finius Eetch

12/16/2006 3:15:00 AM

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.
4 Answers

Ron de Bruin

12/16/2006 10:43:00 AM

0

Hi Finius

See
http://www.contextures.com/xlautofilter02....

--

Regards Ron de Bruin
http://www.rondebruin.n...


"Finius Eetch" <FiniusEetch@discussions.microsoft.com> wrote in message news:15A38BF3-9808-4387-832C-9C71EC862033@microsoft.com...
> 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.
>
>
>

Finius Eetch

12/16/2006 11:01:00 AM

0

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" wrote:

> Hi Finius
>
> See
> http://www.contextures.com/xlautofilter02....
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.n...
>
>
> "Finius Eetch" <FiniusEetch@discussions.microsoft.com> wrote in message news:15A38BF3-9808-4387-832C-9C71EC862033@microsoft.com...
> > 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

12/16/2006 11:22:00 AM

0

You can use this with code

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


--

Regards Ron de Bruin
http://www.rondebruin.n...


"Finius Eetch" <FiniusEetch@discussions.microsoft.com> wrote in message news:E77BBF1E-A08C-4A7F-868B-E599A294D03F@microsoft.com...
> 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" wrote:
>
>> Hi Finius
>>
>> See
>> http://www.contextures.com/xlautofilter02....
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.n...
>>
>>
>> "Finius Eetch" <FiniusEetch@discussions.microsoft.com> wrote in message
>> news:15A38BF3-9808-4387-832C-9C71EC862033@microsoft.com...
>> > 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.
>> >
>> >
>> >
>>

Finius Eetch

12/16/2006 6:53:00 PM

0

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

"Ron de Bruin" wrote:

> You can use this with code
>
> total_records_found = application.WorksheetFunction.Subtotal(......................)
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.n...
>
>
> "Finius Eetch" <FiniusEetch@discussions.microsoft.com> wrote in message news:E77BBF1E-A08C-4A7F-868B-E599A294D03F@microsoft.com...
> > 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" wrote:
> >
> >> Hi Finius
> >>
> >> See
> >> http://www.contextures.com/xlautofilter02....
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.n...
> >>
> >>
> >> "Finius Eetch" <FiniusEetch@discussions.microsoft.com> wrote in message
> >> news:15A38BF3-9808-4387-832C-9C71EC862033@microsoft.com...
> >> > 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.
> >> >
> >> >
> >> >
> >>
>
>