Count of filtered rows

Hi,

I have run into this scenario before but wanted to post this here.

I am enclosing a file to help show what i am talking about.

I have a macro i am working on and i filter the worksheet on a column. I filter on this column such that i want to count all the filtered rows (if any) which do not equal "Level2".

Then i use the following to count the number of rows of filtered rows........

Please Login or Register  to view this content.

Generally speaking the above code works well .......until today.

For some reason i am getting last_row_pst_filter = 75 and i dont even have (as far as i know) 75 rows in the file.

Any ideas? Why does it show 75?

I'm thinking you need to add VBA code that hides BLANKS in the filter. Just a thought.

- Moo

hi,

shouldnt what i have take care of blanks........because the filter should show everything that does not equal "Level2".....i thought this would include blanks as well.

can you show me what you mean and i will try it.

thanks for responding.

harry, i get a run time error 91 when i try your code

i will take a look at it in the morning.....i did open the file and run it and it did run fine so i must be doing something wrong.........thanks. i will revisit this in the morning.....kinda tired.

HSV, it does not work. It does work when there are visible elements that are filtered but if there are no visible elements filtered it does not work.

hi,

HaHoBe, i have not checked out your code yet but i will.

What i did was take HSV's code and put some error checking around it and it SEEMS to do what i want. Here is the code i used

Please Login or Register  to view this content.

This seems to work..........if there are no visible filtered elements i need to know this and this seems to work.


I always seem to run into issues using the autofilter.........does anyone know of any good documents/articles which talks about using this via vba?

HSV,

I was wondering if you could explain a little (not too much) this code

Please Login or Register  to view this content.

Not sure i understand the "offset" and ".specialcells(12)" or .specialcells(2)


jeffreybrown,
I dont know the last row/cell........so i dont understand how that code will help me. But thanks for posting.......i will look at it again but i dont think it applies .........

HSV,

Quick question about
"Specialcells(2) = xlcelltypeconstants"

What does this mean exactly?

hi HSV,

Thanks.

My reason for using autofilter is that its a "quick" way of actually deleting data when you have a large file. My biggest issue with using autofilter is i always seem to run into a "problem" or "issue" where i have not taken into account some special "thing". The last time this happened i thought i finally got a good soln using auto filter........and then this latest "thing" happened.

Can you think of any thing where the soln you proposed would not work? Just trying to think ahead...

thanks again to ALL who responded.........thank you very much.

Over the last 3yrs ihave learned so much from this site..........and i just keep learning..........

thanks.

One last word/question/comment:

Last night i was doing some reading and i found this statement in a book that actually fixed my problem........i think.

My original issue was that when counting the number of visible rows after a filter i got a value that did not make sense.......i would get a value of say 20 when there were essentially no rows visible except the header row. What i found out in the book was this

When using the "specialcells" method one must be careful. The specialcells method includes data that was contained in the "current session" even if you have deleted that information. To "reset" one must save or execute an "activesheet.usedrange"...........

Using the ActiveSheet.UsedRange solved my problem...........

What i did not realize is that before "filtering" my data i had deleted some rows i did not want. I now did an "ActiveSheet.UsedRange" to reset prior to the use of specialcells method and the count of visible rows was correct. Without the "ACtiveSheet.UsedRange" the count of visible rows i think included the deleted rows i had done previously.

Just thought i would pass this on.

One more question i did have though. I NEVER had this problem before until i got a new computer about 2wks ago........do you think there was some setting in my old computer that i didnot have in my new computer? Just another thought.