How can I erase all non-locked cells in a protected worksheet with one keystroke

%3CLINGO-SUB%20id%3D%22lingo-sub-1687716%22%20slang%3D%22en-US%22%3EHow%20can%20I%20erase%20all%20non-locked%20cells%20in%20a%20protected%20worksheet%20with%20one%20keystroke%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1687716%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20clear%20all%20cells%20in%20a%20worksheet%20which%20are%20not%20locked%2C%20with%20one%20keystroke%3F%26nbsp%3B%20Or%20is%20there%20another%20attribute%20I%20can%20assign%20to%20specific%20cells%20so%20that%20I%20can%20leave%20them%20intact%20while%20clearing%20all%20cells%20without%20this%20attribute%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1687716%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1687794%22%20slang%3D%22de-DE%22%3ESubject%3A%20How%20can%20I%20erase%20all%20non-locked%20cells%20in%20a%20protected%20worksheet%20with%20one%20keystroke%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1687794%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796111%22%20target%3D%22_blank%22%3E%40RLevkoy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ESince%20there%20can%20be%20many%20ways%20to%20find%20a%20solution%2C%20allow%20me%20to%20show%20you%20two%20of%20these%20ways%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSTRONG%3E%3CSPAN%20class%3D%22%22%3EA.%20without%20VBA%20the%20following%20workaround%20may%20be%20feasible%3A%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3E%201.%20Mark%20all%20unlocked%20cells%20while%20holding%20down%20the%20CTRL%20key%20so%20that%20all%20are%20marked%20%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3E%202.%20Give%20this%20cell%20range%20a%20name%2C%20the%20easiest%20way%20is%20to%20enter%20the%20name%20in%20the%20address%20field%20(top%20left%20next%20to%20the%20editing%20line).%20%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3E%203.%20Now%20you%20can%20select%20all%20these%20cells%20at%20once%20at%20any%20time%20by%20selecting%20this%20name%20in%20the%20address%20field%20again%20(drop-down!)%20%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3E%204.%20Now%20you%20can%20delete%20all%20cells%20with%20one%20click.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EB.%20with%20VBA%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EDelete%20sub%20()%0ADim%20z%20As%20Range%2C%20sh%20As%20Worksheet%0Achoice%20%3D%20MsgBox%20(%22Do%20you%20really%20want%20to%20empty%20all%20unprotected%20cells%20in%20all%20sheets%2C%22%20_%0A%26amp%3B%20vbCrLf%20%26amp%3B%20vbCrLf%20%26amp%3B%20%22AND%22%20_%0A%26amp%3B%20vbCrLf%20%26amp%3B%20vbCrLf%20%26amp%3B%20%22delete%20all%20current%20data%3F%22%2C%20vbYesNo%2C%20Title%3A%20%3D%20%22_%0ASet%20everything%20to%20zero%20%22)%0AIf%20choice%20%3D%20vbYes%20Then%0AFor%20Each%20sh%20In%20ActiveWorkbook.Worksheets%0AFor%20Each%20z%20In%20sh.UsedRange%0AIf%20z.MergeArea.Locked%20%3D%20False%20Then%20z.MergeArea.ClearContents%0ANext%20z%0ANext%20sh%0AMsgBox%20%22Your%20data%20has%20been%20deleted!%22%2C%2C%20Title%3A%20%3D%20%22All%20sheets%20are%20now%20at%20zero%20_%0Aposed%20!%22%0AElse%3A%20MsgBox%20%22Your%20data%20was%20not%20deleted!%22%2C%2C%20Title%3A%20%3D%20%22No%20new%20year%20_%0Astarted!%20%22%0AEnd%20If%0AEnd%20Sub%0A%0A'Formulas%20translation%20from%20German%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there a way to clear all cells in a worksheet which are not locked, with one keystroke?  Or is there another attribute I can assign to specific cells so that I can leave them intact while clearing all cells without this attribute?

2 Replies

@RLevkoy 

 

Since there can be many ways to find a solution, allow me to show you two of these ways

 

A. without VBA the following workaround may be feasible:
1. Mark all unlocked cells while holding down the CTRL key so that all are marked
2. Give this cell range a name, the easiest way is to enter the name in the address field (top left next to the editing line).
3. Now you can select all these cells at once at any time by selecting this name in the address field again (drop-down!)
4. Now you can delete all cells with one click.

 

B. with VBA

 

Delete sub ()
Dim z As Range, sh As Worksheet
choice = MsgBox ("Do you really want to empty all unprotected cells in all sheets," _
& vbCrLf & vbCrLf & "AND" _
& vbCrLf & vbCrLf & "delete all current data?", vbYesNo, Title: = "_
Set everything to zero ")
If choice = vbYes Then
For Each sh In ActiveWorkbook.Worksheets
For Each z In sh.UsedRange
If z.MergeArea.Locked = False Then z.MergeArea.ClearContents
Next z
Next sh
MsgBox "Your data has been deleted!",, Title: = "All sheets are now at zero _
posed !"
Else: MsgBox "Your data was not deleted!",, Title: = "No new year _
started! "
End If
End Sub

'Formulas translation from German

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

@RLevkoy ,,,

 

I would like to suggest two possible methods, one is the simplest through commands and another is the fastest VBA Macro.

 

Method 1:

  1. From HOME Tab find & hit,, Find & Replace.
  2. Click Options button to expand the dialog box.
  3. Now hit Format button, then Find Format dialog box will appear.
  4. Under Protection tab, un-check Locked and Hidden boxes.
  5. Click OK to return to the Find and Replace dialog box.
  6. Now click Find All.

 

All the unprotected cells will display in the list box.

 

  1. Press Ctrl + A to select all listed data. 
  2. Close the Find & Replace dialogue to return to the sheet.

 

Now you find all unlocked cells have been selected. 

 

  1. Press Delete to remove them.

 

Method 2:

  1. To open the VB editor Press ALT+F11.
  2.  Copy & Paste this code as standard module.
  3. Now press ALT+Q to return to the Sheet.
  4. Save the Workbook as Macro Enabled ( .xlsm ).
  5. RUN the Macro.

 

 

Sub ClearUnlockedCells()

    Dim WorkRange As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    
    For Each Cell In WorkRange
        If Cell.Locked = False Then Cell.Value = ""
    Next Cell

End Sub