Auto filter

Angela - Southport

New Member
Joined
Sep 20, 2002
Messages
1
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?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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.

Code:
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

Well-known Member
Joined
Feb 15, 2002
Messages
575
you could use a subtotal above the first row to count them something like
=SUBTOTAL(3,B3:B2000)
 

Forum statistics

Threads
1,144,208
Messages
5,723,020
Members
422,475
Latest member
Lawiltshire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top