Status Bar display with Autofilter 1

[mgp]

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

 

[joerd]

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

 

[mgp]

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

 

[joerd]

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

 

[mgp]

Thanks joerd

I just realised that

I will change the code

regards
Mogens