Auto filter

[Angela - Southport]

At work I use Auto filter to extract records that meet certain criteria. Usually the status bar tells me how many records have been filtered. This is a feature that is important to me. Some of my spreadsheets have stopped giving me this information and just say 'Filter Mode' instead of the number of filtered records. What am I doing to cause this to happen and how can I put it right?

 

[dk]

Hello,

You'll only get the count of filtered records as long as you don't do something which changes the status bar - unfortunately, this means doing almost anything. You have a couple of options that I can think of:-

Apply the filter again

or

Use this macro to tell you the number of visible records. To use it, open the Visual Basic Editor (Alt F11), click Insert, Module and then paste the code below. You can the run it by choosing Alt F8 from Excel's main menu.

Sub GetFilteredRecords()
Dim lngTotalRecords As Long
Dim lngFilteredRecords As Long

lngTotalRecords = ActiveSheet.UsedRange.Rows.Count - 1
lngFilteredRecords = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Cells.Count / _
            ActiveSheet.UsedRange.Columns.Count - 1
MsgBox lngFilteredRecords & " of " & lngTotalRecords & " records found.", vbInformation, "Filter Results"

End Sub

_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-09-21 10:04
This message was edited by dk on 2002-09-21 10:05

 

[Paul B]

you could use a subtotal above the first row to count them something like
=SUBTOTAL(3,B3:B2000)

 

[dk]

Hello,

Also found this whilst over on the Excel newsgroup:-

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