Solved

Count the autofilter

Posted on 2008-10-30
3
618 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
need count numbers IN ranges 25 30
EXCEL String-handling question ... 7 48
sort time order 10 42
Get average for unknown number of cells per degree.. 3 35
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

737 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