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.

--
Regards,

OssieMac


"OssieMac" wrote:

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"))


--
Regards,

OssieMac


"Jessica New Forest" wrote:

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!