locked
SpecialCells(xlCellTypeVisible).Rows.Count Doesn't return correct visible Rows Count RRS feed

  • Question

  • Hi Every one,

    I have following rows data in a Table name "tSource" below:

    I am applying folowing filter in VBA

    Range("tSource").AutoFilter 2, "=FS*"

    cCnt = Range("tSource").SpecialCells(xlCellTypeVisible).Rows.Count

    The filter apply correctly and I can see all the Visible rows apply to criteria but the Count return me wrong number for visible rows. Don't know why.

    Entity Code
    104 FS 101
    104 FS 102
    104 FS 103
    104 FS 104
    104 FS 105
    104 FS 106
    104 FS 107
    104 FS 111
    104 FS 112
    104 FS 113
    104 FS 114
    104 N04 433
    104 N04 434
    104 N04 435
    104 N04 436
    104 N04 441
    104 N04 442
    104 N04 443
    104 N04 444
    104 N04 445
    104 N04 446
    104 N07 101
    105 FS 101
    105 FS 102
    105 FS 103
    105 FS 104
    105 FS 105
    105 FS 106
    105 FS 107
    105 FS 111
    105 FS 112
    105 FS 113
    105 FS 114
    105 N04 433
    105 N04 434
    105 N04 435
    105 N04 436
    105 N04 441
    105 N04 442
    105 N04 443
    105 N04 444
    105 N04 445
    105 N04 446
    105 N07 101
    • Moved by Kee Poppy Monday, October 17, 2011 5:53 AM (From:Visual Basic IDE)
    Thursday, October 13, 2011 10:18 PM

All replies

  • Hi minddew,

    We have Visual Basic for Applications forum would fit this question better. I will move this thread there for getting more responses.

    Thanks,


    Kee Poppy [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 17, 2011 5:53 AM
  • You cannot count rows in a filtered range with hidden rows. The count stops after the first non contiguous row.

    there are a number of ways of addressing the problems associated with this but first need to know what you propose to do. eg. do you want to copy the visible data or do you need to process the visible data on each row of a particular column.


    Regards, OssieMac
    Monday, October 17, 2011 6:47 AM
  • Count them the same way you would in the worksheet:

     

    Sub CountVisible()
    Dim r As Range
    Set r = Intersect(ActiveSheet.AutoFilter.Range, Range("A:A"))
    MsgBox Application.WorksheetFunction.Subtotal(103, r) - 1
    End Sub

     This should work if column A is fully populated

     


    gsnu201109

    • Edited by Gary's Student MVP Monday, October 17, 2011 11:19 AM typo
    • Proposed as answer by JaguarXII Saturday, September 19, 2015 4:27 PM
    Monday, October 17, 2011 10:48 AM
  • On Thu, 13 Oct 2011 22:18:47 +0000, minddew wrote:
     
    >
    >
    >Hi Every one,
    >
    >I have following rows data in a Table name "tSource" below:
    >
    >I am applying folowing filter in VBA
    >
    >Range("tSource").AutoFilter 2, "=FS*"
    >
    >cCnt = Range("tSource").SpecialCells(xlCellTypeVisible).Rows.Count
    >
    >The filter apply correctly and I can see all the Visible rows apply to criteria but the Count return me wrong number for visible rows. Don't know why. Entity Code
     
    Count only applies to one area at a time.  The default will be area 1.  You need to loop through all the areas in order to get the total count.
     
    So something like:
     
    ====================
    Option Explicit
    Sub CountFiltered()
      Dim rfiltered As Range
      Dim rArea As Range
      Dim cCnt As Long
       
    Range("tSource").AutoFilter 2, "=FS*"
    Set rfiltered = Range("tSource").SpecialCells(xlCellTypeVisible)
     
    For Each rArea In rfiltered.Areas
        cCnt = cCnt + singleArea.Rows.Count
    Next rArea
     
    Debug.Print cCnt
     
    End Sub
    =====================
     
    And be sure to account for the title rows in your count.
     

    Ron
    Monday, October 17, 2011 12:13 PM
  • On Mon, 17 Oct 2011 10:48:23 +0000, Gary's Student [MVP] wrote:
     
    >This should work if column A is fully populated
     
    And it won't if any of the filtered rows happen to be blank in column A
     

    Ron
    Monday, October 17, 2011 7:48 PM
  • On Mon, 17 Oct 2011 12:13:29 +0000, Ron Rosenfeld <ron@nospam.net> wrote:
     
    >On Thu, 13 Oct 2011 22:18:47 +0000, minddew wrote:   > > >Hi Every one, > >I have following rows data in a Table name "tSource" below: > >I am applying folowing filter in VBA > >Range("tSource").AutoFilter 2, "=FS*" > >cCnt = Range("tSource").SpecialCells(xlCellTypeVisible).Rows.Count > >The filter apply correctly and I can see all the Visible rows apply to criteria but the Count return me wrong number for visible rows. Don't know why. Entity Code   Count only applies to one area at a time.  The default will be area 1.  You need to loop through all the areas in order to get the total count.   So something like:   ==================== Option Explicit Sub CountFiltered()   Dim rfiltered As Range   Dim rArea As Range   Dim cCnt As Long     Range("tSource").AutoFilter 2, "=FS*" Set rfiltered = Range("tSource").SpecialCells(xlCellTypeVisible)   For Each rArea In rfiltered.Areas     cCnt = cCnt + singleArea.Rows.Count Next rArea   Debug.Print cCnt   End Sub =====================   And be
    >sure to account for the title rows in your count.  
    >Ron
     
    Typo Alert:
     
    Should read:
     
    =============================
    Option Explicit
    Sub CountFiltered()
      Dim rfiltered As Range
      Dim rArea As Range
      Dim cCnt As Long
       
    Range("tSource").AutoFilter 2, "=FS*"
    Set rfiltered = Range("tSource").SpecialCells(xlCellTypeVisible)
     
    For Each rArea In rfiltered.Areas
        cCnt = cCnt + rArea.Rows.Count
    Next rArea
     
    Debug.Print cCnt
     
    End Sub
    =====================
     

    Ron
    Monday, October 17, 2011 7:50 PM
  • My question to the OP still stands. It depends on the reason for wanting the number of rows. It could well be that it is not required and there are better ways of achieving the end result. Having said that the following counts the rows without looping and irrespective of whether there are empty cells.

    Dim cCnt As Long

    cCnt = ActiveSheet.AutoFilter.Range.Columns(1) _
      .SpecialCells(xlCellTypeVisible).Cells.Count

    While counting rows does not work on filtered ranges; counting cells does work so just count the cells in a single column of the filtered range.

    Following added with edit.

    It should be understood that code like the following cannot be used in filtered ranges because it would return the values of the filtered and unfiltered data in rows up to the value of cCnt.

    For r = 1 To cCnt
      MsgBox Cells(r, 1).Value
    Next r

    However, code like the following can be used. (Note for one column only).

    For Each c In ActiveSheet.AutoFilter.Range.Columns(1) _
        .SpecialCells(xlCellTypeVisible)
      MsgBox c.Value
    Next c

    Offset can then be used to address cells other columns in the filtered range.


    Regards, OssieMac
    • Edited by OssieMac Monday, October 17, 2011 8:41 PM
    Monday, October 17, 2011 8:13 PM
  • While counting rows does not work on filtered ranges; counting cells does work so just count the cells in a single column of the filtered range.
    Regards, OssieMac
    Good point.  And it doesn't matter if the cells are empty or not.

    Ron
    Monday, October 17, 2011 8:35 PM
  • Thank for for moving it to the right place.
    Tuesday, October 18, 2011 3:41 PM
  • The Code doesn't worked, I mean the column(1).Cells.count. I am guess as I have multiple Columns in tables and the result of Columns(1).cells.count return different number. If I try to add a variation of ActiveSheet.AutoFilter.Range.Columns(1) .SpecialCells(xlCellTypeVisible).Cells.ROWS.count same result return as I stated earlier.

    I did got my results for conventional loop to check for FS and add to count but was wondered that why its not working properly. I really appreciate you time.

     

    Tuesday, October 18, 2011 3:58 PM
  • On Tue, 18 Oct 2011 15:58:17 +0000, minddew wrote:
     
    >
    >
    >The Code doesn't worked, I mean the column(1).Cells.count. I am guess as I have multiple Columns in tables and the result of Columns(1).cells.count return different number. If I try to add a variation of ActiveSheet.AutoFilter.Range.Columns(1) .SpecialCells(xlCellTypeVisible).Cells.ROWS.count same result return as I stated earlier.
    >
    >I did got my results for conventional loop to check for FS and add to count but was wondered that why its not working properly. I really appreciate you time.
    >
     
    Did you try counting by Areas, as I suggested, and did that work?
    --Ron
     

    Ron
    Tuesday, October 18, 2011 6:36 PM
  • The Code doesn't worked, I mean the column(1).Cells.count. I am guess as I have multiple Columns in tables and the result of Columns(1).cells.count return different number. If I try to add a variation of ActiveSheet.AutoFilter.Range.Columns(1) .SpecialCells(xlCellTypeVisible).Cells.ROWS.count same result return as I stated earlier.

    I did got my results for conventional loop to check for FS and add to count but was wondered that why its not working properly. I really appreciate you time.

     


    What do you mean by doesn't work? Do you get an error or it returns the incorrect value?

    In your original post you mention tables but because of the code sample I assumed that tSource was a named range. If it is actually a table then you should refer to it as a ListObject like the code below.

    Also why do you need the number of rows? Post a sample of how you intend using the returned value cCnt. There might be other ways of achieving your end result.

    What version of Excel are you using? (Just in case that might be the problem)

    Example if Autofiltered range is actually a table.

    cCnt = ActiveSheet.ListObjects("tSource").AutoFilter.Range.Columns(1) _
      .SpecialCells(xlCellTypeVisible).Cells.Count

     Following added with edit.

    For processing data in AutoFiltered ranges I have been using the cells.count method and For Each ...... method that I posted previously for quite a long time without problems in worksheets and more recently (since Excel 2007) the above code in tables so I know the code works.


    Regards, OssieMac
    • Edited by OssieMac Wednesday, October 19, 2011 7:57 PM
    Tuesday, October 18, 2011 8:27 PM
  • Then:

     

    Sub CountVisibleRows()
    Dim rTable As Range, r As Range, Kount As Long
    Set rTable = ActiveSheet.AutoFilter.Range
    Kount = 0
    For Each r In Intersect(Range("A:A"), rTable)
        If r.EntireRow.Hidden = False Then
            Kount = Kount + 1
        End If
    Next
    MsgBox Kount - 1
    End Sub

     

     


    gsnu201109
    Tuesday, October 18, 2011 10:57 PM
  • Well, i just found this page.

    we can use a worksheet function Subtotal(3,RangeToCountForVisible) and assign the value to a variable.

    RangeToCountForVisible is one column, 3 parameter says to function to count the nonempty cells.

    Of course we must apply a fliter previously...

    Hope it may help someone....


    Tuesday, October 29, 2013 8:33 PM
  • A tiny trick! Introduce a "helper" column in the filtered range. For example if we are filtering a single column like column A, then in column B,cell B2 enter:

    =SUBTOTAL(3,$A$2:A2) and copy down.

    Before any filtering is applied, this looks like a simple sequential list of integers. Once filtering has been applied the column still shows a simple sequential numbering of the visible rows. The count of the visible rows is then just: =MAX(B:B) You can also use column B to help retrieve data from any specific visible row (using the usual MATCH / INDEX approach)


    gsnu201308


    Tuesday, October 29, 2013 10:06 PM
  • Inspired on Minddew's sub, this worked for me for any filtering conditions:  

    Function CountFiltered(rFiltered As Range, lastRow As Long) As Long
      Dim rArea As Range
      Dim cCnt As Long

    For Each rArea In rFiltered.Areas
        If rArea.Row <= lastRow Then
            If rArea.Row + rArea.Rows.Count > lastRow Then
                cCnt = cCnt + (lastRow - rArea.Row) + 1 
            Else
                cCnt = cCnt + rArea.Rows.Count
            End If
        End If
    Next rArea

    CountFiltered = cCnt

    End Function

    Note: rFiltered is already the visible range after filtering, and lastRow is the... last row with data in the column (without it, I was getting a last area with valid, plus one and a half million rows 'till the end of the worksheet)

    Wednesday, March 14, 2018 11:18 AM