Results 1 to 5 of 5

Thread: Counting rows with autofilter on

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jan 2009
    Posts
    20

    Counting rows with autofilter on

    I usually use Selection.Rows.count to count the number of active rows....however if you are using an autofilter it will count all rows, not the visible rows.....can anyone help?

  2. #2
    Junior Member Akos_beres's Avatar
    Join Date
    Mar 2009
    Location
    Minneapolis, MN
    Posts
    24

    Re: Counting rows with autofilter on


  3. #3
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Counting rows with autofilter on

    To count visible rows:

    On worksheet, you can use
    =SUBTOTAL(3, Range("A2:A1000"))

    In VBA code:
    VisibleRows = WorksheetFunction.Subtotal(3, Range("A2:A1000"))
    You can also use
    Range("A2:A1000").SpecialCells(xlCellTypeVisible)
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  4. #4
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

    Re: Counting rows with autofilter on

    Rather than start a new thread, I am extending this thread as I have the same problem and the solutions suggested above do not work.

    Open a new workbook, throw some values into column 1 of worksheet 1, and paste the following code in a new module. Make sure the values do not include "999" to make the error occur.

    Code:
    Private Sub Test()
    
        Dim XL_Ws As Excel.Worksheet
        Dim Last_Row As Long
        
        Set XL_Ws = ThisWorkbook.Worksheets(1)
        
        'Turn off any existing autofilter (otherwise row and col values may be zero)
        XL_Ws.AutoFilterMode = False
        
        'Determine last used row
        If WorksheetFunction.CountA(XL_Ws.Cells) > 0 Then
            Last_Row = XL_Ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        End If
        
        'Apply autofilter to header
        XL_Ws.Range("A1").AutoFilter Field:=1, Criteria1:=999
        
        'Check if filtered range is empty
        If XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Rows.Count <> 0 Then
            'Do main bit of code here
        End If
    
    End Sub
    The problem is that the line which checks if there filtered range is empty generates an error itself! I get "Run-time error 1004: No cells were found."

    I have tried several variations of this line of code to no avail:

    Code:
    If XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Count <> 0 Then
    
    If XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Count - 1 <> 0 Then
    
    If XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Rows.Count Is Nothing Then
    
    If IsError(XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Rows.Count) Then

    The worst part is that all the help forums I've read tell me that these solutions are supposed to work..... E.g.

    www.contextures.com
    www.ozgrid.com
    www.ozgrid.com (2)
    www.mrexcel.com
    Etc.....


    If possible, I would like to avoid using Excel-specific worksheet functions.

  5. #5
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

    Re: Counting rows with autofilter on

    I still have not been able to solve this.

    In addition to the above attempts, I have also tried capturing the error by evaluating the problematic code with functions like IsError and IsNull but VB seems to handle the expression first before the function, which also results in the same error. E.g.

    Code:
    If IsError(XL_Ws.Range(XL_Ws.Cells(2, 1), XL_Ws.Cells(Last_Row, 1)) _
    .SpecialCells(xlCellTypeVisible).Rows.Count) Then

    I don't understand what I'm doing wrong.
    Last edited by Earlien; Feb 9th, 2012 at 07:02 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width