count rows after autofilter

**opie546** · 10-26-2012, 01:15 PM

Hello Everyone,

I am trying to count the number of occurrences of 1,2,3, and H separately in a column.

I have a excel document that contains information on parts (part number, data, status, date entered). In the status column, there is a possibility of 1,2,3,H - i have managed to filter all of the data by date entered by a user. I have tried countif and several other options. The problem is that this macro will be run from a different workbook, and will open this worksheet to count the occurrences of 1,2,3,H and then it will display the data on the original workbook.
Top level design, my macro will be run on a spreadsheet called "master metrics" it will open and filter the data by the specified date on the "partdata" spreadsheet. Once it has done that, it will count and return the occurrence of 1,2,3,H individually and sum it up. I do not have a problem filtering by date, I have done that. Currently i am not worrying about the different spreadsheets because that is just a matter of putting in the filename/path. The problem I am currently having is the counting.

I am trying to count the visible cells before and after filtering by date:

Please Login or Register  to view this content.

but it is not working properly, it returns 1725, when the correct value is 1720 - there is some data that got filtered out prior to this and should NOT be counted, is this the issue? how do i count only the filtered data?
doing this returns 1048576

Please Login or Register  to view this content.

then
I cannot decide which method is more efficient:

filtering by "3" then

Please Login or Register  to view this content.

rows 1 and 2 are both header/information rows

another option is to use countif, which is much more efficient but I am not sure if it will work across different spreadsheets/workbooks

another option is to run a loop through all of the rows and then increment a different variable everytime one of the values is read - if i were to do this option i guess i would not need to count the number of rows because i could just use a

Please Login or Register  to view this content.

loop and then add all of the different status variables together to get the Total

I am just learning VBA so any insight you can provide would be wonderful,

Thank you!

**brynbaker** · 10-26-2012, 02:11 PM

Here is a quick suggestion: to sum the visible rows in a filtered set, use =Subtotal(103,<range>). This does a counta but only visible cells.

**HaHoBe** · 10-26-2012, 02:18 PM

Hi, opie456,

I would consider the use of a Pivot Table for showing the data wanted.

Ciao,
Holger

**opie546** · 10-26-2012, 02:32 PM

thank you for your responses! brynbaker: i need to store the number of visible rows as a variable to be used later on and to display in a different workbook
Holger: i was able to implement your code and it works, but I do not understand what it is doing, thank you!

anyone have any suggestions as to how I should attack the rest of the problem? should I use one loop to count everything? filtering is proving to be a very slow method, even with only about 1000 columns, eventually these master spreadsheets will be populated to the 10,000+ range, so looking towards the future, should I use one loop to increment variables based on the data in each cell?

**opie546** · 10-26-2012, 02:35 PM

Originally Posted by HaHoBe

Hi, opie456,

I would consider the use of a Pivot Table for showing the data wanted.

Ciao,
Holger

as of this point, I do not need to display that data, i only need to sort through and count the occurrences of 1,2,3,H

I was able to get the original code you suggested to work