×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-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!

*Eng-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

Status Bar display with Autofilter

Status Bar display with Autofilter

Status Bar display with Autofilter

(OP)
I have a spreadsheet with an autofilter applied.

When I set the filter, the staus bar shows "Filter Mode"

On other spreadsheets with filtered lists, the status bar shows "xx of yy records found"

Why doesn't my first sheet show the same and how do I change it to make the status bar show number of found records.

Any suggestions?

Mogens

RE: Status Bar display with Autofilter

Excel displays the xx of yy records found message only at the time that you apply the autofilter. As soon as you do something else (save the workbook, clear a cell, for example), the status bar goes back to "Filter mode" as long as the filter is on. You can get the message back by reapplying the filter (pick from a drop down box.)

I tried some visual basic code (see below)to have the number of records shown all the time. You can put it in a sheet event procedure, such as Worksheet_SelectionChange, so it will update the statusbar every time you change the selection on the sheet. However, this causes the statusbar to remain fixed at the previous value when you apply a different filter.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim r As Range, i As Integer
    For Each r In ActiveSheet.AutoFilter.Range.Rows
        If Not r.Hidden Then i = i + 1
    Next r
    If ActiveSheet.FilterMode Then
        Application.StatusBar = CStr(i - 1) & " of " & CStr(ActiveSheet.AutoFilter.Range.Rows.Count - 1) & " records found."
    Else
        Application.StatusBar = False
    End If
End Sub


If you just want the number of records that match given criteria, I suggest you take a look at the COUNTIF function, or use an advanced filter (see Excel Help).
Good luck,

Regards,

Joerd

RE: Status Bar display with Autofilter

(OP)
Thanks for the reply

Shame there is no FilterChange event but your code will definitely help me. (anyway else to trigger the macro when reapplying filter?)

I have used the countIF but my list is large and there are many different requests for filters.

regards
Mogens

RE: Status Bar display with Autofilter

mgp:

I found that the activesheet.autofilter gives an error if you remove the autofilter from the sheet. I suggest you change the code to:


Dim r As Range, i As Integer
    If ActiveSheet.FilterMode Then
        For Each r In ActiveSheet.AutoFilter.Range.Rows
            If Not r.Hidden Then i = i + 1
        Next r
        Application.StatusBar = CStr(i - 1) & " of " & CStr(ActiveSheet.AutoFilter.Range.Rows.Count - 1) & " records found."
    Else
        Application.StatusBar = False
    End If

Regards,

Joerd

RE: Status Bar display with Autofilter

(OP)
Thanks joerd

I just realised that

I will change the code

regards
Mogens

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 Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

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

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



News


Close Box

Join Eng-Tips® Today!

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

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close