asked on

Count the autofilter

Hi,

I need a VBA code that helps counting different categories in an autofilter.
Let's say in my sheet autofiltered I needed to copy all the rows that have
in their column E the word 'Commerce'.
What I am doing right now is recording a macro that selects from the autofilter
the filed 'Commerce' and copies the whole sheet from A:2 until I:65000
but this is completely unefficient. since I have to do this again and again for many many fields
and keeping in mind that my original doc has a lot of rows!

So basically, I need a code that overcomes the fact that my commerce field
is made up of: row 2,3,13,21,22,23,24,34,42,43
for instance and I want that code to return 10 rows rather than 43 in the count.

Thanks for the help
Listing.xls

Wayne Taylor (webtubbs)

Hi hugoohta,

One way is to evaluate the SUBTOTAL function, calling the COUNTA function....

Dim VisRows as Long : VisRows = [SUBTOTAL(3, A:A)]

Regards,

Wayne

hugoohta

ASKER

What I am using right now is that code:

Sheets("autofiltered").AutoFilterMode = False
Sheets("autofiltered").Rows("1:1").AutoFilter
Sheets("autofiltered").Rows("1:1").AutoFilter Field:=5, Criteria1:="'Commerce' "
On Error Resume Next
Sheets("autofiltered").Range("A1:I59999").Copy Sheets("Sheet2").Range("A59999").End(xlUp).Offset(0, 0)

How can I designate the exact amount of rows I need to copy and paste in sheet2

Thanks

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial