Thursday, May 7, 2015

Excel get autofilter row count

After having this question myself and doing a little bit of searching, I've found that there's a lot of misinformation out there, so I'm going to post a reliable way to get the number of visible records in an Excel autofiltered worksheet.

Public Function GetRecordCount(ws As Worksheet) As Long
    GetRecordCount = ws.AutoFilter.Range.Columns(1).SpecialCells( _
        xlCellTypeVisible).Count - 1
End Function

This is the most common incorrect solution to this problem I've come across:

' This code is incorrect.
row_count = filter.Range.SpecialCells(xlCellTypeVisible).Rows.Count

The Rows property does not work correctly on Ranges that have multiple Areas, such as a range returned from the SpecialCells method. The property only returns the rows in the first Area of the Range, so you will only get a row count of the first contiguous range of the filtered data. E.g., if rows 1, 2, 5, 6, 7, and 8 are visible, the result would be 2, since rows 1 and 2 comprise the first contiguous range.

The other approach that I've seen uses a for loop to count rows. As I've discussed before, in VBA you always want make as few calls to the Excel API as possible. You never want to operate on an Excel worksheet one row at a time when the API exposes a method to get the same result using a single call, because operating one row or cell at a time will kill your macro performance when you are processing a lot of data.

No comments:

Post a Comment