×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Counting the results from an Excel autofilter

Counting the results from an Excel autofilter

Counting the results from an Excel autofilter

(OP)
Does anyone know an easy way to display the number of records produced by an autofilter?

It would be relatively easy to knock up a macro to count the rows, but perhaps there's something lurking in Excel which would make it easier.

Paul Milnes

RE: Counting the results from an Excel autofilter

Milnes,
You can use your status bar to display the count of the number of records in your autofilter.  First make sure it's displayed.  If not, go the the View menu and click  status bar. If it is displayed, look at the right end of the bar where the word "NUM" usually is displayed.  Right click at word and select Count.  Count will display the number of records in the autofilter on your status bar. Not sure if this is what you were looking for, but hope it helps.

RE: Counting the results from an Excel autofilter

Aug4570:

I tried your suggestion but the NUM continues to be displayed not the actual number of rows returned by the autofilter.

Do I need to set some other option to make this work?

Larry De Laruelle
larry1de@yahoo.com

RE: Counting the results from an Excel autofilter

Many thanks, AUG4570. That works a treat on my machine. However the colleague who asked me about it can't make it work, so I wonder if LarryDeLaruelle is right about some other option being required?.

Incidentally, Larry - look over on the left-hand side of the status bar. You may have missed it. (Sorry if this is obvious!)

Paul Milnes

RE: Counting the results from an Excel autofilter

Thanks Paul:

Sometimes the obvious is easily overlooked and I would have felt pretty much the fool if you had been correct.

Not this time, however.  All I see on the left side of the Status Bar is "Ready" and "Num" on the right.

I checked all the settings in Tools/Options and could not find anything that looked like a toggle to turn the display on or off.

This has me intrigued now.  Does anyone have an idea on what setting has to be enabled to make the autofilter display a count is the status bar?

Larry De Laruelle
larry1de@yahoo.com

RE: Counting the results from an Excel autofilter

Ok, let me throw another twist into this mix.

I contacted a friend who teaches an Excel class at a local University.  He sent me a sample workbook that he uses in class as an Autofilter example.  When I opened it and set the Autofilter it displayed the results as expected in the status bar:  "xx of yy records found".  However, my workbook with Autofilter applied still does not show this on the status bar.

Unfortunately, when I called him for an explanation of this odd behaviour he did not have an answer.  So much for modern education.  (Actually, he's an excellent instructor and I think I've piqued his interest enough that he'll continue to look for an answer.)

Given this, it seems that the issue may be a setting specific to the workbook rather than for Excel in general.  I'm just stumped on what it might be and where to find it and the Excel Help does not.

Whoever figures this out should write a FAQ for the board.  Who knows, it might be worth a dinner.

Larry De Laruelle
larry1de@yahoo.com

RE: Counting the results from an Excel autofilter

Hey there,
I believe you're right Larry when stating that it may be the workbook settings instead of Excel.  I opened another Excel worksheet that I'd gotten elsewhere and could not get my status bar to activate on this particular worksheet. I could not figure out why this was happening. I have no problems with any of my other worksheets. Okay, I'm stumped.  BTW, the status bar (when you can get it to work)works for any selected group of cells, not only for autofilters.   

RE: Counting the results from an Excel autofilter

(OP)
Found it! It appears to be a bit of a bug (shurely- 'undocumented feature' - Ed) in Excel. Here is the Knowledge base article URL:

http://support.microsoft.com/support/kb/articles/Q189/4/79.ASP?LN=EN-US&SD=gn&FR=0

I haven't been able to reproduce the error myself, possibly because my worksheet doesn't fall foul of any of the problems outlined in the KB article.

Paul

RE: Counting the results from an Excel autofilter

Paul:

That did the trick.  

Thanks for the tip.

Larry De Laruelle
larry1de@yahoo.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close