Row.Count counts invisible cells even when using xlCelltypevisible

user_11435155 0
Viewed 130

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
row count vba excel
4 Answers

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
user_98140690

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.

user_4788840

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
user_99127140

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)

Alternative Solution

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
user_116134260
Related