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

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

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

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

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