Solved

Count the autofilter

Posted on 2008-10-30
3
613 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 47

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 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Suggested Solutions

This collection of functions covers all the normal rounding methods of just about any numeric value.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 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