Macro for counting the number of filtered rows

[DGillham]

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(xlVisible).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

 

[Norman Jones]

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.

 

[DGillham]

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

 

[Norman Jones]

Hi Darren,

If you insert a sutable Subtotal formulas, these will dynamically respond as
the data is filtered.

 

[DGillham]

Thanks Norman, I see what you mean. I will try this now.

Thanks again

Darren