Excel - Undo set filters 1

[Bill4tektips]

Does anyone know if there is a way to undo any set filters in an Excel spreadsheet? I have a large spreadsheet used by several people and someone always leaves a filter on and unless you have good eyes it's hard to spot the Blue arrow from the Black.

 

[Loomah]

not entirely sure what you're asking but
data>filter>show all
clears the filters

i have the 'show all' button on a custom commandbar as i'm far too lazy for the menu route!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Hi,

Right-Click in the toolbar.

Select Customize...

Select the Commands Tab.

Select the Data Category.

Drag Show All Icon to an existing toolbar.

VOLA! y'all!

I also have the AutoFilter Icon right next to it in my toolbar.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bill4tektips]

Thanks Skip, that works but I was looking for a Macro to do it when the file opens. If you have to depend on people doing it manually they just won't do it, then I get a phone call asking what is wrong so a Macro would help.

 

[Loomah]

I also have the AutoFilter Icon right next to it in my toolbar.

me too - spooky or what or do i just need to get out more.....

With Worksheets(1)
If .AutoFilterMode And .FilterMode Then .ShowAllData
End With

in the workbook_open event should do the trick

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

I'm lazy...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  on error resume next
  sh.showall
  on error goto 0
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bill4tektips]

Skip
Where do I put this code please?

 

[SkipVought]

In the ThisWorkbook Object Code Window

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bill4tektips]

Sorry Skip but it doesn't work

 

[SkipVought]

sh.showall[b]data[/b]

duh! sorry

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bill4tektips]

Skip, Works fine now. Thanks for your patience

 

[SkipVought]

And likewise, greatful for yours as well.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}