Solved

Get the number of records found by autofilter in excel

Posted on 2013-12-11
14
1,021 Views
Last Modified: 2013-12-12
Dear experts,
WHen you apply an autofilter to a list, you see in the status bar at the bottom something like "4 of 17 records found"
How do I put this into a variable ( I want my variable to become 4 if the autofilter found 4 records. the 4 must be somewhere already,so how do you go straight to that? so please do not give me something where I loop through the worksheet.

So my code is:
dim strCrit as string
dim n long
strCrit="something"

ActiveSheet.Range("A:J").AutoFilter Field:=8, Criteria1:=strCrit
n= .....
what gives me the number of found records?
0
Comment
Question by:gstallaert
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39712665
I think this knowledge base article is the exact scenario you're looking for :)
http://support.microsoft.com/kb/152215

Sub Filter_Return()
    Sheets("sheet1").Select
    Range("a1").Select
    Selection.CurrentRegion.Select
    row_count = Selection.Rows.Count - 1     ' Count the rows and
                                             ' subtract the header.

    ' The following three lines run an AutoFilter using "Cat" as the
    ' criteria for the first column and greater than 0 as the
    ' criteria for the second column.
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="Cat"
    Selection.AutoFilter Field:=2, Criteria1:=">0"
    matched_criteria = 0                         ' Set variable to
                                                 ' zero.
    check_row = 0                                ' Set variable to
                                                 ' zero.
    While Not IsEmpty(ActiveCell)            ' Check to see if row
                                             ' height is zero.
        ActiveCell.Offset(1, 0).Select
        If ActiveCell.RowHeight = 0 Then
            check_row = check_row + 1
        Else
            matched_criteria = matched_criteria + 1
        End If
    Wend

    If row_count = check_row Then            ' If these are equal,
                                             ' nothing was returned.
        MsgBox "no matching data"
    Else
        MsgBox matched_criteria - 1          ' Display the number
                                             ' of records returned.
    End If

End Sub

Open in new window

0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39712671
Apparently you can also do it without a macro (depending on your needs):
Use the same sample data from step 2, above. If the AutoFilter is currently on, point to Filter on the Data menu, and then click AutoFilter.

In cell C8, type the following formula:
      =SUBTOTAL(3,C2:C6)
						
NOTE: The first argument for the Subtotal function is the function used to calculate the subtotal. The argument in this example uses the Count function (3) to calculate the subtotal.

Select cell A1, point to Filter on the Data menu, and then click AutoFilter. 

This turns on the AutoFilter, and you get a drop-down box for each of the three fields in this example.

Click the drop-down box in the Animal field, and then click Cat in the drop-down list. 

The AutoFilter filters all the records with Cat in the Animal field, and in this example, the result of the Subtotal function in cell C8 is 2.

Open in new window


...this is assuming the data set is in these cells:
       A1:  Animal     B1:  In Stock         C1:  Price
       A2:  Dog        B2:  1                C2:  $1.00
       A3:  Cat        B3:  2                C3:  $2.00
       A4:  Dog        B4:  3                C4:  $3.00
       A5:  Cat        B5:  4                C5:  $4.00
       A6:  Bird       B6:  5                C6:  $5.00

Open in new window

0
 
LVL 29

Expert Comment

by:gowflow
ID: 39712703
Hi it is not a straight way but will get you there.

This is the code

Sub test()
Dim strCrit As String
Dim n As Long
Dim WS As Worksheet
Dim rng As Range

strCrit = "n34"
n = -1

Set WS = ActiveSheet
WS.Range("A:J").AutoFilter Field:=8, Criteria1:=strCrit
For Each rng In WS.UsedRange.EntireRow
    If Not rng.EntireRow.Hidden Then
        n = n + 1
    End If
Next rng
MsgBox ("total rows visible " & n)


End Sub

Open in new window


The workbook attached as an example
gowflow
Autofilter-count.xls
0
 
LVL 1

Author Comment

by:gstallaert
ID: 39712838
Very nice, guys, but you are all looping through the data. My question is: isn't there some property of the filtered worksheet or so that gives the number of found records directly?
Or is there a way to getthe value of what is shown in the status bar?, not re-creating the value (using a loop), but reading what is there, through code
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39712865
Does this work?
Sub Filter_Return()
    Dim rng As Range
    Dim result As Integer
    
    Set rng = ActiveSheet.Range("A:J")
    rng.AutoFilter Field:=8, Criteria1:=strCrit
    
    result = Application.Subtotal(9, rng.Columns("A"))
    MsgBox(result)
End Sub

Open in new window

0
 
LVL 29

Expert Comment

by:gowflow
ID: 39713349
AngelPlay
Did you try it ? I did and it does not work !!! try it on my sample data it does not work !
gowflow
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39713464
You can use specialcells:

ActiveSheet.Range("A:J").AutoFilter Field:=8, Criteria1:=strCrit
n = Activesheet.Range("A:A").Specialcells(xlcelltypevisible).count - 1

Open in new window


The -1 is to exclude the header row.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 29

Expert Comment

by:gowflow
ID: 39713491
rorya I tried this too but it does not work !!! it returns 1 always this is why I had to recourse to looping excluding hidden rows.
gowlfow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39713500
rorya no matter what you do this instruction
n = Activesheet.Range("A:A").Specialcells(xlcelltypevisible).count - 1

returns
 0
or returns
 65529
or returns
 1
I enven tried all of the following:
n = Activesheet.Range("A:A").End(xlup).Specialcells(xlcelltypevisible).EntireRow.count - 1

or
n = Activesheet.Range("A:A").End(xlDown).Specialcells(xlcelltypevisible).EntireRow.count - 1

the way your formula is done calculate the number of visible cells !!! not rows and still give wrong figure.

gowflow
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 39713516
Apologies - wasn't paying attention to the ranges being whole columns. You need:
ActiveSheet.Range("A:J").AutoFilter Field:=8, Criteria1:=strCrit
n = Activesheet.Autofilter.Range.Columns(1).Specialcells(xlcelltypevisible).count - 1

Open in new window

0
 
LVL 29

Expert Comment

by:gowflow
ID: 39713536
rorya u truly deserve the SAVANT rank if not more !
Fantastic I was banging my head against the wall

Amazing this Excel never cease to impress us ! what is the magic that behind Columns you get a correct count and otherwise it turns dumb ???

gowflow
0
 
LVL 1

Author Closing Comment

by:gstallaert
ID: 39713538
Thanks a lot!
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39713706
Using Activesheet.Autofilter.Range restricts the range to just the range that is actually filtered, then using Columns(1) refers to just the first column so that we can use a straight cell count (rather than row count, which won't work with multiple area ranges).
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39713727
tks for always enlighten us in the right direction !!!
gowflow
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel 2010 Text Formatting placing a hyphen in front of text 3 20
increment numbers by 10 11 31
Most Consistent Performer 4 22
tricky if formula 2 0
This article will show you how to use shortcut menus in the Access run-time environment.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now