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.
Usage:
Where:
"A" - is the column or first column in the column lists.
20 - is the start row of the data set.
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
Post a Comment