excelvbaloopsrangevisible

Count Visible Rows in Filtered Worksheet


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

Solution

  • 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
    

    enter image description here

    enter image description here

    enter image description here