Displaying Counts when Filtering

[Tony M]

Usually when using the filter function, the total records
in the spreadsheet and the number of records that match
the filter criteria are displayed on the lower left of
the window (I guess it's the status bar). However, for
certain files that I have, no counts are displayed when I
filter. Does anyone know if there is a setting in the
file that causes this or something else that needs to be
changed? I do notice this happens frequently with files
created by exporting from Access, but not always. It
also happens with other files, but I can't find a common
denominator.

 

[Shah Shailesh]

Hi Tony,

See this KB Article

http://support.microsoft.com/default.aspx?scid=kb;en-
us;Q189479

You may assigne this macro to your toolbar button
with\without shortcut key.

Assume your data starts from Column A.


Sub CountRecords()

Dim rng As Range, totrec As Long, fr As Long

On Error GoTo xit

If Not ActiveSheet.AutoFilterMode Then Exit Sub

Set rng = ActiveSheet.AutoFilter.Range

With rng

totrec = .Rows.Count - 1

fr = .Columns(1).SpecialCells(xlVisible).Count - 1

MsgBox fr & " Records found.", , "Total Records = " &
totrec

'find sum
MsgBox "Sum =" & Application.Subtotal(9, .Columns
(ActiveCell.Column)), , rng(1, ActiveCell.Column)

End With

Exit Sub

xit:

MsgBox Err.Description,,"Error Found"

Err.Clear

End Sub



Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)

 

[JMay]

I prefer a formula approach:
=subtotal(3,H2:H1000) in Cell H1001 say
HTH

 

[Dave Peterson]

Debra Dalgleish has some techniques at:
http://www.contextures.com/xlautofilter02.html#Count

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;213886&Product=xlw
XL2000: Excel AutoFilter Status Bar Message Shows "Filter Mode"

http://support.microsoft.com/default.aspx?scid=kb;en-us;189479&Product=xlw
AutoFilter Status Bar Message Shows "Filter Mode"