Solved

Count the autofilter

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

785 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