How to get number of filtered records found displayed in a cell?

[Jessica New Forest]

Having created several macros to run filters in my spreadsheet on the click
of a button for each category, I now want the number of records found with
each macro to be displayed in a table on the same worksheet, for an end of
month report. i'm sure there's a simple way to do it!

 

[OssieMac]

Hi Jessica,

The worksheet function SUBTOTAL returns lots of maths results on filtered
lists. See Help for more info. You can use them on a worksheet but they are
dynamic and change each time the filter is changed. However, you can also use
them in VBA. When using in VBA you need to specify that it is a worksheet
function in the code. The results can be copied to cells in the worksheet to
generate a report like you have described.

Example to count the records:-

Dim countFiltered As Long

countFiltered = WorksheetFunction.Subtotal(3, Range("B2:B29"))

 

[joel]

Does this help

Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set DataRange = Range("A1:A" & Lastrow)
VisibleRows = DataRange.Cells.SpecialCells(xlCellTypeVisible).Rows.Count

 

[OssieMac]

Hi joel,

That code only counts the rows until it comes to the first hidden row. Note
that it includes the column header but nothing from the first non visible
row down. I realize that one would expect it to work but for some reason it
does not.

I have previously tried the following method and it has the same problem.

countVisibleRows = Sheets("Sheet1").AutoFilter.Range. _
SpecialCells(xlCellTypeVisible).Rows.Count

The total cells can be counted with the following. Note that the count
includes the column header cells as would the above code if it worked:-

countVisibleCells = Sheets("Sheet1").AutoFilter.Range. _
SpecialCells(xlCellTypeVisible).Cells.Count

columns count also works with the above code.

One of life's mysteries I suppose.

 

[OssieMac]

Hi Again Jessica,

I probably should have qualified a couple of things for you. The code can be
applied to any column of your filtered data. It does not have to be the
column with the actual filter.

It does not count blanks so try to select a column that will not have blank
cells after a filter is applied.

In the formula, include the full number of rows in the un-filtered range of
data excluding the column header. Can include extra rows if you want in case
you add data later. Can even use say B2 to the last row of the worksheet.