Trying to allow autofilter to be cleared with VBA code?

slibao 

The "Clear Filter" icon is greyed out when the sheet is protected.

You might need to work around this limitation by allowing users to clear the filter using a custom button or macro.

Here is a workaround using a custom button:

1. Insert a Button (Form Control) on the worksheet.
• Go to the "Developer" tab (if not visible, you may need to enable it in Excel's options).
• Click on the "Insert" drop-down in the "Controls" group.
• Select "Button (Form Control)" and draw a button on the worksheet.
2. Right-click the button, choose "Edit Text," and give it a label like "Clear Filter."
3. Right-click the button, select "Assign Macro," and create a new macro (e.g., "ClearFilter") or choose an existing one.
4. In the VBA editor, write a macro to clear the filter:

Vba code:

Sub ClearFilter()
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
End Sub

See the example file.

5. Now, when users click the "Clear Filter" button, it will run the "ClearFilter" macro and clear the filter even when the sheet is protected.

This approach allows users to clear the filter without needing to unprotect the entire sheet. It provides a more user-friendly way to manage filters, especially when the sheet is protected to prevent changes to other cells.