Excel VBA: Clear an Advanced Filter on a Protected Worksheet
Question details
The user needs to clear and reapply an Advanced Filter with VBA while the worksheet is protected.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A Worksheet_Change event should respond to criteria changes, remove the current AutoFilter, apply new AdvancedFilter criteria, and restore worksheet protection.
- Observed behavior
- Worksheet protection prevents VBA from clearing or changing the current filter unless the sheet is temporarily unprotected.
Save a backup copy of the workbook, confirm the worksheet password, and verify the criteria and data ranges used by the macro. The code must be placed in the worksheet module that contains the protected data.
Temporarily unprotect the worksheet before clearing and applying the filter
Use this method when worksheet protection blocks ShowAllData or AdvancedFilter during a Worksheet_Change event.
The macro removes protection before changing the filter and protects the worksheet again afterward. Replace the sample password and adjust the criteria and data ranges to match your workbook.
ShowAllData can fail when no filter is currently active, so the example safely ignores that specific clearing error before continuing.
In Excel, press Alt+F11 to open the Visual Basic Editor. In the Project Explorer, double-click the worksheet that contains the criteria cells and filtered data.
Paste this code into the worksheet module, then replace "Password" with the actual worksheet password and update E6:F7 and E10:E8000 if your ranges differ: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.CountLarge > 1 Then Exit Sub Dim rgCriteria As Range Set rgCriteria = Me.Range("E6:F7") If Not Intersect(Target, rgCriteria) Is Nothing Then Me.Unprotect Password:="Password" On Error Resume Next Me.ShowAllData On Error GoTo 0 Me.Range("E10:E8000").AdvancedFilter _ Action:=xlFilterInPlace, _ CriteriaRange:=rgCriteria Me.Protect Password:="Password", UserInterfaceOnly:=True End If End Sub
Close the Visual Basic Editor with Alt+Q, enable macros if prompted, and change a cell in E6:F7. Excel should clear the existing filter, apply the new AdvancedFilter criteria, and protect the worksheet again.
Apply protection settings when the workbook opens
Use this option when you want macros to keep working on a protected sheet after every workbook restart.
Use WPS Office to manage protected-sheet VBA filters
WPS Office Spreadsheets can open compatible macro-enabled workbooks and provides a familiar spreadsheet interface for reviewing worksheet protection, filter criteria, and VBA code. When macro support is available in your WPS edition, use the VBA editor to temporarily unprotect the sheet, clear the filter, apply AdvancedFilter, and protect it again.
- 1. Open the macro-enabled workbook: Launch WPS Office, choose Open, and select the compatible .xlsm workbook containing the protected worksheet.
- 2. Open the VBA editor: Use the Tools or Developer menu to open the VBA editor, then select the worksheet module containing the filter event.
- 3. Update the filter procedure: Add the Me.Unprotect, Me.ShowAllData, AdvancedFilter, and Me.Protect calls, replacing the sample password and ranges with your workbook values.
- 4. Test and save the workbook: Change a filter criteria cell, confirm that the filter is reapplied while protection returns, and save the workbook in its compatible macro-enabled format.
Frequently Asked Questions
Why does ShowAllData fail on a protected worksheet?
Worksheet protection can prevent VBA from changing the filter state. Call Me.Unprotect before Me.ShowAllData, then protect the worksheet again after AdvancedFilter finishes.
What should replace the sample Password value?
Replace both occurrences of "Password" with the actual worksheet protection password. If the sheet has no password, use Me.Unprotect and Me.Protect without the Password argument.
Why is On Error Resume Next used around ShowAllData?
ShowAllData can raise an error when no filtered list is currently displayed. Limiting On Error Resume Next to that call lets the macro continue while avoiding hidden errors in the rest of the procedure.
Does UserInterfaceOnly remain enabled after closing Excel?
No. UserInterfaceOnly is not reliably persisted when the workbook closes, so apply it again in a Workbook_Open event if the macro must edit the protected sheet after each restart.
Where should the Worksheet_Change code be placed?
Press Alt+F11, expand Microsoft Excel Objects, and place the code in the specific worksheet module whose criteria cells trigger the filter—not in a standard module.
