How to Get the Count of Filtered Sets of Data Rows in Excel using VBA

The following function below allows you to get the number of visible rows from a filtered sets of rows in Excel using VBA. The function takes two arguments which is the Column and StartRow. Calling the FilterCount() function returns the number of visible rows. Also added an error handler which process the error description to determine if there's a visible row.

Parameters:
Column: The column of the data to be filtered. If you have multiple columns being filtered you can just set the first Column or any column in the dataset.
        StartRow: Start row of the data to be filtered.

Function FilterCount(ByVal Column As String, ByVal StartRow As Long) As Long

    On Error GoTo errHandler
  
    FilterCount = Application.WorksheetFunction.CountA(ActiveSheet.Range(Column & StartRow, Cells(ActiveSheet.UsedRange.Rows.Count, Range(Column & StartRow).Column)).SpecialCells(xlCellTypeVisible))

    'Debug.Print FilterCount
  
Exit Function
errHandler:
    If Err.DESCRIPTION = "No cells were found." Then
        'Set count to 0
        FilterCount = 0
    End If
  
    'Debug.Print FilterCount
  
End Function

Usage: 

Sub Count()
    Debug.Print FilterCount "A", 20
End Sub

Where:
          "A" - is the column or first column in the column lists.
           20  - is the start row of the data set.

Comments

Popular posts from this blog

How to Create a Configuration.INI Files in VB6

How to Set Windows Form Always on Top of Other Applications in VB6

How to Minimize/Send an App Icon to System Tray in Visual Basic 6