Row.Count counts invisible cells even when using xlCelltypevisible-CodePudding

I am trying to filter a table that at times can result in no data. I am trying to count the rows visible to make this determination.

If only a Header row - "No Cells" If has visible rows - "Has Provider"

In the code below it seems to count the filtered rows still...

Sub Add_New_Name()

    Dim pTable1 As Range
    Dim pVisible As Range

    'Application.DisplayAlerts = False
    'Application.ScreenUpdating = False
            
    ' Select Roster & Clear Roster Table Filters
    Sheet8.Activate
    Sheet8.ListObjects("Table1").AutoFilter.ShowAllData
                    
    ' Set Variables
    Set pTable1 = Range("B2").CurrentRegion
    Set pVisible = pTable1.SpecialCells(xlCellTypeVisible)
                    
    ' Check for New Associate
    With Sheet8.ListObjects("Table1")
        .Range.AutoFilter Field:=23, Criteria1:="0"
        .Range.AutoFilter Field:=22, Criteria1:="Associate"
    End With
                    
    If pVisible.Rows.Count > 1 Then
        MsgBox "No Cells"
    Else
        MsgBox "Has Provider"
    End If
                    
End Sub

CodePudding user response：

Are There Any Filtered Rows?

You can use On Error Resume Next as illustrated in Tim Williams' answer.
You cannot use the rows count on a non-contiguous range because it only refers to the range's first area. So if the first data row is not visible, it will return 1 no matter how many rows are visible after.
But you can use the cells count on a non-contiguous single column range.

Option Explicit

Sub Add_New_Name()
    
    Application.ScreenUpdating = False
    
    Dim cc As Long
    With Sheet8.ListObjects("Table1")
        If .ShowAutoFilter Then ' remove filter
            If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        End If
        .Range.AutoFilter Field:=23, Criteria1:="0"
        .Range.AutoFilter Field:=22, Criteria1:="Associate"
        ' Get the cells count of any single column range!
        cc = .ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Cells.Count
        .AutoFilter.ShowAllData ' remove filter
    End With
    
    Application.ScreenUpdating = True ' before the message box
    MsgBox IIf(cc = 1, "No Cells", "Has Provider")
    
End Sub

CodePudding user response：

Here's how it's typically done:

Sub Add_New_Name()
    Dim pVisible As Range

    Sheet8.Activate
    With Sheet8.ListObjects("Table1")
        .AutoFilter.ShowAllData
        .Range.AutoFilter Field:=23, Criteria1:="0"
        .Range.AutoFilter Field:=22, Criteria1:="Associate"
        
        On Error Resume Next 'ignore error if no visible rows
        Set pVisible = .DataBodyRange.SpecialCells(xlCellTypeVisible) 'ignore headers
        On Error GoTo 0      'stop ignoring errors
    End With
    
    If pVisible Is Nothing Then
        MsgBox "No Cells"
    Else
        MsgBox "Has Provider"
    End If
End Sub

It's cleaner to exclude the headers from the call to SpecialCells and trap/ignore the error if no rows are visible.

That way if you go on to work with pVisible you don't have the headers in there.

CodePudding user response：

This is the most straight forward method for me:

 VisibleRowCount = WorksheetFunction.SUBTOTAL(103, Sheet2.ListObjects("Table1").ListColumns(22).DataBodyRange)

But if your not sure if the table is empty or not use:

If not Sheet2.ListObjects("Table1").DataBodyRange is Nothing then _
   VisibleRowCount = WorksheetFunction.SUBTOTAL(103, Sheet2.ListObjects("Table1").ListColumns(22).DataBodyRange)

Addendum

As VisualBasic2008 pointed out:

If you have just filtered the column by a value, then it's perfectly safe to use SubTotal.

In retrospect, I would include the entire ListColumn.Range and -1 from the count to avoid errors.

 VisibleRowCount = WorksheetFunction.SUBTOTAL(103, Sheet2.ListObjects("Table1").ListColumns(22).Range) -1

CodePudding user response：

Set pVisible after the autofilter.

With Sheet8.ListObjects("Table1")
    .Range.AutoFilter Field:=23, Criteria1:="0"
    Range.AutoFilter Field:=22, Criteria1:="Associate"
End With

Set pVisible = pTable1.SpecialCells(xlCellTypeVisible)

If you are not going to use pVisible for anything else, make it an integer.

Dim pTable As ListObject      'use instead of range
Dim rows_visible As Integer   'pVisible alternative

Set pTable = Sheet8.ListObjects("Table1")

With pTable              
    .Range.AutoFilter Field:=23, Criteria1:="0"
    .Range.AutoFilter Field:=22, Criteria1:="Associate"                    
End With

rows_visible = pTable.Range.SpecialCells(xlCellTypeVisible).Rows.Count
'Stores number of rows that are visible. 
'1 means only header. 
'>1 means some info is present.

If rows_visible > 1 Then
    MsgBox "Has Provider"
Else
    MsgBox "No Provider"
End If