Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get number of filtered records found displayed in a cell?
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get number of filtered records found displayed in a cell?
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get number of filtered records found displayed in a cell?
Does this help
Lastrow = Range("A" & Rows.Count).End(xlUp).Row Set DataRange = Range("A1:A" & Lastrow) VisibleRows = DataRange.Cells.SpecialCells(xlCellTypeVisible).Ro ws.Count "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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get number of filtered records found displayed in a cel
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. -- Regards, OssieMac "joel" wrote: Does this help Lastrow = Range("A" & Rows.Count).End(xlUp).Row Set DataRange = Range("A1:A" & Lastrow) VisibleRows = DataRange.Cells.SpecialCells(xlCellTypeVisible).Ro ws.Count "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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to get number of filtered records found displayed in a cel
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change left bottom note that tell number records found? | New Users to Excel | |||
Autofill doesn't give the number of records filtered any more | Excel Worksheet Functions | |||
How to obtain the number of filtered records in Autofilter | Excel Worksheet Functions | |||
how do I display filtered records found | Excel Discussion (Misc queries) | |||
Advanced Filter: Number of Records Found | Excel Discussion (Misc queries) |