Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for counting the number of filtered rows
Hi I am using an autofilter on my data and need to be able to add a total at the bottom of that filter that tells me how many rows are included in the filter. Microsoft kindly provides the following code: Dim UpperLeftCorner As Range ' UpperLeftCorner should be set to the upper-left ' corner of the list range: Set UpperLeftCorner = Sheets("Sheet1").Range("A4") RowCount = -1 For Each area In _ UpperLeftCorner.CurrentRegion.SpecialCells(xlVisib le).Areas RowCount = RowCount + area.Rows.Count Next MsgBox RowCount This works fine, by rather than a message box I would like to actually add this total to the worksheet - any ideas how this can be done? Thanks Darren -- DGillham ------------------------------------------------------------------------ DGillham's Profile: http://www.excelforum.com/member.php...o&userid=13707 View this thread: http://www.excelforum.com/showthread...hreadid=499343 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for counting the number of filtered rows
Hi Darren,
Check the SubTotal function in Excel help. For example: =SUBTOTAL(3,A1:A100)-1 would return the number of filtered rows where the header row is row 1 and a filter is applied to column A. --- Regards, Norman "DGillham" wrote in message ... Hi I am using an autofilter on my data and need to be able to add a total at the bottom of that filter that tells me how many rows are included in the filter. Microsoft kindly provides the following code: Dim UpperLeftCorner As Range ' UpperLeftCorner should be set to the upper-left ' corner of the list range: Set UpperLeftCorner = Sheets("Sheet1").Range("A4") RowCount = -1 For Each area In _ UpperLeftCorner.CurrentRegion.SpecialCells(xlVisib le).Areas RowCount = RowCount + area.Rows.Count Next MsgBox RowCount This works fine, by rather than a message box I would like to actually add this total to the worksheet - any ideas how this can be done? Thanks Darren -- DGillham ------------------------------------------------------------------------ DGillham's Profile: http://www.excelforum.com/member.php...o&userid=13707 View this thread: http://www.excelforum.com/showthread...hreadid=499343 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for counting the number of filtered rows
Thanks for your help. This works almost 100% ,but the problem is that I need to perform the same filter process on various spreasheets. Sometimes it may be a 100 rows filtered, maybe one at another time. What I really need is for the filter to be performed and then the total number of rows left to appear at the bottom of this filtered list. Hope someone can help. Darren -- DGillham ------------------------------------------------------------------------ DGillham's Profile: http://www.excelforum.com/member.php...o&userid=13707 View this thread: http://www.excelforum.com/showthread...hreadid=499343 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for counting the number of filtered rows
Hi Darren,
If you insert a sutable Subtotal formulas, these will dynamically respond as the data is filtered. --- Regards, Norman "DGillham" wrote in message ... Thanks for your help. This works almost 100% ,but the problem is that I need to perform the same filter process on various spreasheets. Sometimes it may be a 100 rows filtered, maybe one at another time. What I really need is for the filter to be performed and then the total number of rows left to appear at the bottom of this filtered list. Hope someone can help. Darren -- DGillham ------------------------------------------------------------------------ DGillham's Profile: http://www.excelforum.com/member.php...o&userid=13707 View this thread: http://www.excelforum.com/showthread...hreadid=499343 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for counting the number of filtered rows
Thanks Norman, I see what you mean. I will try this now. Thanks again Darren -- DGillham ------------------------------------------------------------------------ DGillham's Profile: http://www.excelforum.com/member.php...o&userid=13707 View this thread: http://www.excelforum.com/showthread...hreadid=499343 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of unique values in a filtered list | Excel Discussion (Misc queries) | |||
Help Please-Counting Filtered Rows | Excel Worksheet Functions | |||
counting filtered rows ... | Excel Programming | |||
Is there a limit to the number of rows that can be filtered | Excel Worksheet Functions | |||
counting filtered rows | Excel Programming |