Solved

Get the number of records found by autofilter in excel

Posted on 2013-12-11
14
1,265 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
[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
  • 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 31

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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 31

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
 
LVL 31

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 31

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 31

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 31

Expert Comment

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

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

615 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