Pinoy Tutorials, Tips and Tricks

<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQqtclCvU__dI_BwgPRkloW55GwVWVcQQoly8EItRBgNmyK22mVAWQhaCB0l4L4OzxYUMvSCRclt-fF5bCpEsTGfKyVKVZys0BAlq4TTMwwAi_yx0d1GpUmGX5hVAKV4WSBJJ0JLcbUXsu/s1600/excel-vba-filter-count.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="165" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQqtclCvU__dI_BwgPRkloW55GwVWVcQQoly8EItRBgNmyK22mVAWQhaCB0l4L4OzxYUMvSCRclt-fF5bCpEsTGfKyVKVZys0BAlq4TTMwwAi_yx0d1GpUmGX5hVAKV4WSBJJ0JLcbUXsu/s1600/excel-vba-filter-count.png" width="200" /></a></div>
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. 
 
<pre class="brush: vb">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</pre>
 
Usage:  
 
<pre class="brush: vb">Sub Count()
 Debug.Print FilterCount "A", 20
End Sub</pre>
 
Where: 
          "A" - is the column or first column in the column lists. 
           20  - is the start row of the data set. 
<div>
</div>
</div>

excel microsoft excel VBA 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 t...

Pinoy Tutorials, Tips and Tricks

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

Popular Posts

TAG CLOUD

Blog Archive