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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

--

Regards,

Peo Sjoblom

"lothario" wrote in message
...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

Thank you.

This helped.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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




lothario wrote:

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

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson