Solved

Count the autofilter

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

624 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