3

Even though Microsoft says that it's not possible: Can't clear auto filter in protected sheet, I am still thinking that somehow this should be possible. At least I hope.

I know that I can clear filters from columns one by one, but I want users to be able to click that Clear feature on the Ribbon/Sort&Filter section. (Currently it's disabled / greyed out)

Please see the screenshot for any misunderstanding.

There are many questions on the internet but none of them is useful honestly. Is there any possibility?

I tried defining Worksheet_Change event to Unprotect Sheet but that is not sensible at all because it slows down my worksheet and I wasn't able to find correct condition to define into Worksheet_Change. In this example it was running whenever $:$ rows are selected . So I need another smart suggestion.

Mertinc
  • 793
  • 2
  • 13
  • 27
  • Why not to set a custom button on the top of the worksheet, make it unprotect the sheet and clear the filters? - a custom Clear per say- – Sgdva Jun 08 '17 at 03:21
  • sgdva, the problem here is, there is another two button at the top of the worksheet which are used very frequently, in the other hand this (data clear) one is not the priority, the users may use it rarely. So I don't want to show it as 3rd button. – Mertinc Jun 08 '17 at 21:02
  • I'm afraid that there's no other easy option. If you really want to invest time and somehow do it with the ribbon, you may want to try with the [Custom UI Editor](https://www.youtube.com/watch?v=jqL7xSa73tk) – Sgdva Jun 08 '17 at 21:23
  • I have already tried adding my macro to the ribbon, that works well, that's ok, but just on my computer, I will watch that video, hopefully it will teach me how to create a ribbon which will also appear on other user's ribbon as well. Thanks for the help. – Mertinc Jun 08 '17 at 21:31
  • Exactly, only on your computer will work with the excel easy solution wizard. The one above, it is applied for the file -so any pc that open it will see the same as you-. – Sgdva Jun 08 '17 at 22:12

1 Answers1

3

You do not need to unprotect the sheet to clear the filter. Simply paste this macro in a module

Sub ClearFilter()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    ws.AutoFilter.ShowAllData
End Sub

And then assign a shortcut key to it.

enter image description here

Now when you press CTRL+SHIFT+C, you will see Autofilter data is reset.

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Hi @Siddharth firstly you have helped me a lot through some another's questions and thank you so much for all that I had no chance to thank you :) now the issue is, there will be many users and I will not be able to inform all of them about shortcuts, so somehow there should be a visual thing everybody can easily see and 'know what it is' that's why my priority is the actual button itself. But as an option, can I be able to add a feature/button to the everyone's ribbon for the same thing? I can add it to my personal but I am not sure about the users. – Mertinc Jun 08 '17 at 21:00
  • 1
    Oh yes :) You can add a button to your file before distributing it. You may want to see [How to add a custom Ribbon tab using VBA?](https://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba/8852767#8852767) – Siddharth Rout Jun 09 '17 at 05:12