Row.Count подсчитывает невидимые ячейки даже при использовании xlCelltypevisible

Установите pVisible после автофильтра.

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

Set pVisible = pTable1.SpecialCells(xlCellTypeVisible)

Альтернативное решение

Dim pTable As ListObject      'use instead of range
Dim pVisible As Range

Set pTable = Sheet8.ListObjects("Table1")

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

On Error Resume Next
    Set pVisible = pTable.DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
'Stores range of visible cells. Does not raise error in case of not data.

If Not pVisible Is Nothing Then    'Checks if some info is present.
    MsgBox "Has Provider"
Else
    MsgBox "No Provider"
End If

Вот как это обычно делается:

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

Лучше исключить заголовки из вызова SpecialCells и перехватить/игнорировать ошибку, если строки не видны.

Таким образом, если вы продолжите работать с pVisible, у вас не будет заголовков.

Есть ли отфильтрованные строки?

• Вы можете использовать On Error Resume Next, как показано в ответе Тима Уильямса.
• Вы не можете использовать количество строк для несмежного диапазона, потому что он относится только к первой области диапазона. Таким образом, если первая строка данных не видна, она вернет 1 независимо от того, сколько строк будет видно после нее.
• Но вы можете использовать количество ячеек в несмежном диапазоне одного столбца.

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

Это самый простой метод для меня:

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

Но если вы не уверены, что таблица пуста или не используется:

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

Приложение

Как указал VisualBasic2008:

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

Оглядываясь назад, я бы включил весь ListColumn.Range и -1 из подсчета, чтобы избежать ошибок.

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