Need shortcut to "Data -> Filter -> Show All"

  • Thread starter Thread starter lothario
  • Start date Start date
L

lothario

Hi,

I have 30 columns (with AutoFilters) and 20,000 rows of data.
The user clicks on various filters to see the necessary subset of
data.

After the user is done with viewing the subset, I need to (re-expose)
show all 30 columns and 20,000 rows.
This would be the equivalent of clicking "Data -> Filter -> Show All".

I have asked the users to click on "Data -> Filter -> Show All" when
they are done.
But they keep forgetting.

How can I make a "shortcut" to Show All ?
Can I put a "Show All" button on the toolbar?
Can this be done with some VBA code? If yes, can you please give me
the VBA code?
Do you have any better suggestions?

Thanks,
Luther
 
One possible way

Sub Reset_Filter()
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.FilterMode Then
On Error Resume Next
sh.ShowAllData
End If
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub
 
Turn on the macro recorder. Do "Data -> Filter -> Show All". Turn
off the recorder. Move the code (w/o Sub and End Sub lines) to the
Workbook_BeforeClose in the ThisWorkbook module.

HTH,
Merjet
 
Not a macro, but I've added the "Show All" icon to my favorite toolbar.
Tools|Customize|Commands Tab|Data Category.
 
Back
Top