Modify ? Tom Ogilvy "count visible filter records"

[Todd F.]

Sub CountVisRows()
'by Tom Ogilvy
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range

MsgBox rng.Columns(1). _
SpecialCells(xlVisible).Count - 1 _
& " of " & rng _
.Rows.Count - 1 & " Records"

End Sub

The above code is from Tom Olgilvy as copied from the
contextures web site.

My questions about this neat code include:

- can I set it up to run in every work book I open (maybe
run from personal)

- can I set it up to run everytime I change a filter on
an auto filter list.

Thanks for the time - I use auto filter almost everyday
and the count of records not showing in the lower
left "because I must have auto calc on" is killing me.

Thanks Todd Frisch

 

[Gjones]

Hi Todd;

You should be able to put it in your Personal.Xls and then
make a custom button by using the toolbars customize
functions.

Since the ability to interrogate the "on sheet change"
event is related to each book and sheet you probably
cannot make it detect when ANY auto filter is changed.

For sheets that you use a lot you can go into the
individual workbook and use the change event to detect the
changes.

That would leave you with the range finder in the Personal
file and the change code in the individual workbooks.

Thanks,

Greg