Count Visible Rows in Filtered Worksheet-CodePudding

I need help with counting the number of visible rows in my worksheet. After I filtered for specific ID numbers in my worksheet I want to count the number of visible rows are available after the filter. That way if after the vba filtered is applied and only the header is available, it won't continue to the next steps. However, my code keeps showing that 0 rows are available when it's not the case...This is what I have so far:

Set dataRG=ws.Range("A1").CurrentRegion
For n = UBound(wsNames) To LBound(wsNames) Step -1

If IsArray(ccNumbers(n)) Then 
    dataRG.AutoFilter 7, ccNumbers(n), xlFilterValues
Else
    dataRG.AutoFilter 7, ccNumbers(n) 
End If
'were all data rows filtered out?
If dataRG.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then

CodePudding user response：

I need help with counting the number of visible rows in my worksheet.

That way if after the vba filtered is applied and only the header is available, it won't continue to the next steps.

Dim TotalVisibleFilteredRows As Long

With dataRG
    TotalVisibleFilteredRows = WorksheetFunction.Subtotal(3, .Range("A1:A" & .Rows.Count))
    
    If TotalVisibleFilteredRows > 1 Then
        '
        '~~> Do what needs to be done
        '
        MsgBox "Total Visible Filtered Rows (Incl Header) : " & TotalVisibleFilteredRows & vbNewLine & _
               "Total Visible Filtered Rows (Excl Header) : " & TotalVisibleFilteredRows - 1
    Else
        MsgBox "Only Header is visible"
    End If
End With