Issue with SpecialCells(xlCellTypeVisible)

[Gringoire]

Hello guys,
I'm using the below code on a Table containing about 25.000 rows

After filtering, about 5000 rows are still visible (I checked directly on my table) but even if the range rtotals still counts 25000 rows, the range rvisible only counts one or few rows (depending from filter arguments).
the size of rvisible seems to be stopped at the first row hidden by the filter.
it seems a strange behavior to me, because "SpecialCells(xlCellTypeVisible)" should skip the hidden rows, not stop at them...

any hint?

Function voceDiStima()

    '*** FILTERING ROWS
    With shRawData.ListObjects("TabRawData").DataBodyRange                           
        .AutoFilter                                                                   
        .AutoFilter Field:=6, Criteria1:="C06065", Operator:=xlFilterValues        
        .AutoFilter Field:=34, Criteria1:="*-II0*", Operator:=xlFilterValues
    End With

Dim rtotal as Long,  rvisible as Long
rtotal= shRawData.ListObjects("TabRawData").DataBodyRange.Columns(8).Rows.Count
rvisible=shRawData.ListObjects("TabRawData").DataBodyRange.Columns(8).SpecialCells(xlCellTypeVisible).Rows.Count
End Function

 

[Akuini]

Try using .Cells instead of .Rows

rvisible=shRawData.ListObjects("TabRawData").DataBodyRange.Columns(8).SpecialCells(xlCellTypeVisible).Cells.Count

 

[Gringoire]

Thanks for your answer, but unfortunately it raise an error 438 - Object doesn't support Property or Method.
Basing on google answers it seems that SpecialCells(xlCellTypeVisible) does not operate on filtered (non-contiguous) ranges

​

 

[Akuini]

It works for me:

Book1
	A	B	C	D
1	Preston	Joe	Maze
3	x	Dustin	Zaiden
5	x	Cory	Leonardo
6	x	Tate	Iker
8
Sheet1

Sub try()

Debug.Print ActiveSheet.ListObjects("Table1").DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count 'result = 3, correct result
Debug.Print ActiveSheet.ListObjects("Table1").DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible).Rows.Count  'result = 1,  incorrect result

End Sub

 

[DanteAmor]

Hi @Gringoire , Akuini's solution works for me, but I've already seen it in other answers, that counting visible cells doesn't work for everyone, maybe it's a version problem.

I propose the following approach:

Function voceDiStima()
  Dim obj As ListObject
  Dim i As Long, rvisible As Long, rtotal As Long

  Set obj = shRawData.ListObjects("TabRawData")
  
  With obj.DataBodyRange
    .AutoFilter
    .AutoFilter Field:=6, Criteria1:="C06065", Operator:=xlFilterValues
    .AutoFilter Field:=34, Criteria1:="*-II0*", Operator:=xlFilterValues
  
    For i = 1 To .Rows.Count
      If .Cells(i, 1).EntireRow.Hidden = False Then
        rvisible = rvisible + 1
      End If
    Next
    
    rtotal = .Columns(8).Rows.Count
  
  End With
  MsgBox "Total " & rtotal & " visible " & rvisible
End Function

Note: With 40,000 records, the response is immediate.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Gringoire]

Hi DanteAmor,
your suggestion works.

thank you!