Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1431
  • Last Modified:

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?
0
gstallaert
Asked:
gstallaert
  • 6
  • 3
  • 3
  • +1
1 Solution
 
Angelp1ayCommented:
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
 
Angelp1ayCommented:
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
 
gowflowCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
gstallaertAuthor Commented:
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
 
Angelp1ayCommented:
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
 
gowflowCommented:
AngelPlay
Did you try it ? I did and it does not work !!! try it on my sample data it does not work !
gowflow
0
 
Rory ArchibaldCommented:
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
 
gowflowCommented:
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
 
gowflowCommented:
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
 
Rory ArchibaldCommented:
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
 
gowflowCommented:
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
 
gstallaertAuthor Commented:
Thanks a lot!
0
 
Rory ArchibaldCommented:
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
 
gowflowCommented:
tks for always enlighten us in the right direction !!!
gowflow
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now