vba code to count visible rows after autofiltering a table

[epb613]

I autofiltered a table. Now I want to count the remaining rows. I searched online and found the following code snippet, but it doesn't work. It should return 500, but it returns 1000 (it's counting cells, not rows). What's wrong? (If I take out the SpecialCells property, it returns 500 like it should, but obviously I need to leave that in.)

MsgBox Range.Rows.SpecialCells(xlCellTypeVisible).Count

Thanks!
Pinny

 

[Simon Lloyd]

Maybe try:

Msgbox Activesheet.Usedrange.SpecialCells(xlCellTypeVisible).Rows.Count

 

[epb613]

Thanks! That fixed it!

(Specifically, what fixed it was moving the Rows property to after the SpecialCells property.)

 

[epb613]

Nevermind - I spoke too soon. Now it's coming back with 1. Here's my exact line of code by the way.

msgbox ActiveSheet.ListObjects("Table_owssvr_1").Range.SpecialCells(xlCellTypeVisible).Rows.Count

 

[Simon Lloyd]

I've never tried that but looking at it maybe remove the RANGE!

 

[epb613]

I tried removing the .Range and got Error 480, Object does not support this property or method. Any other ideas?

Thanks!
Pinny

 

[epb613]

Ok, I found the solution on another website. The problem was that after using the SpecialCells method, you are returned a Range of non-continuous areas, which you can't use the Rows property on.

Here's the solution:

    Dim Number_of_Rows as Integer
    With ActiveSheet.ListObjects(1)
        For Each Line In .Range.SpecialCells(xlCellTypeVisible).Areas
            Number_of_Rows = Number_of_Rows + Line.Rows.Count
        Next
    End With

 

[Marcelo Branco]

Another one

Sub Test()
    Dim rngTable As Range
    Dim rCell As Range, visibleRows As Long
    
    Set rngTable = ActiveSheet.ListObjects("Table_owssvr_1").Range
    
    For Each rCell In rngTable.Resize(, 1).SpecialCells(xlCellTypeVisible)
        visibleRows = visibleRows + 1
    Next rCell
    
    MsgBox visibleRows
End Sub

M.

 

[epb613]

What a need trick Marcelo! Here's the code I wound up using:

Dim myTable as Range
Set myTable = ActiveSheet.ListObjects(1).Range
Number_of_Rows= myTable.Resize(, 1).SpecialCells(xlCellTypeVisible).Count

 

[epb613]

Marcelo, is there any reason you chose to use a For loop instead of the .Count method?