Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Count the autofilter

Posted on 2008-10-30
3
Medium Priority
?
628 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:hugoohta
  • 2
3 Comments
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 22839017
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
0
 

Author Comment

by:hugoohta
ID: 22839065
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
0
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 1000 total points
ID: 22839087
Copy the AutoFilter.Range, which is the visible cells only. To ignore the header row, use something like this....

    With Worksheets("autofiltered")
        Intersect(.[2:65536], .AutoFilter.Range).Copy _
                Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End With

Wayne
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question