Solved

Get the number of records found by autofilter in excel

Posted on 2013-12-11
14
965 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

760 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