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
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
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
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
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
I just realised that
I will change the code
regards
Mogens