Avatar of gstallaert
gstallaertFlag for Belgium asked on

Get the number of records found by autofilter in excel

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?
Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
gowflow

8/22/2022 - Mon
Angelp1ay

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

Angelp1ay

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

gowflow

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
gstallaert

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
Angelp1ay

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

gowflow

AngelPlay
Did you try it ? I did and it does not work !!! try it on my sample data it does not work !
gowflow
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rory Archibald

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.
gowflow

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
gowflow

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
gowflow

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
ASKER
gstallaert

Thanks a lot!
Rory Archibald

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).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gowflow

tks for always enlighten us in the right direction !!!
gowflow