Forum Discussion

RLevkoy's avatar
RLevkoy
Copper Contributor
Sep 19, 2020

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

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?

excel

17 Replies

Sort By
  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Sep 20, 2020

    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

     

     

     

    • Raqune's avatar
      Raqune
      Copper Contributor
      Feb 17, 2023

      Rajesh_Sinha 

      Really appreciate your simple clean code to clear unlocked cells! 

      Question: How might I modify this function so clicking on button (described below) will bring up a dialog asking "Are you sure you want to clear the form?" thereby avoiding premature clearing of data.

       

      Adding single-click button to activate "Method 2" macro by Rajesh_Sinha:  

      INSERT>SHAPE to create desired button shape then right-click on inserted shape & select "Format Shape..." from drop down to adjust look of button.  Then right-click again and select "Assign Macro...", select "ClearUnlockedCells" then click "OK".  Now button will activate the macro.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 17, 2023

        Raqune 

        Here is a modified version of the macro by Rajesh_Sinha 

        Sub ClearUnlockedCells()
    Dim WorkRange As Range
    Dim UnlockedRange As Range
    Dim Cell As Range
    If MsgBox("Are you sure you want to clear the form?", vbYesNo + vbQuestion) = vbYes Then
        Set WorkRange = ActiveSheet.UsedRange
        For Each Cell In WorkRange
            If Not Cell.Locked Then
                If UnlockedRange Is Nothing Then
                    Set UnlockedRange = Cell
                Else
                    Set UnlockedRange = Union(UnlockedRange, Cell)
                End If
            End If
        Next Cell
        If Not UnlockedRange Is Nothing Then
            UnlockedRange.ClearContents
        End If
    End If
End Sub
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 19, 2020

    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)

    • Raqune's avatar
      Raqune
      Copper Contributor
      Feb 17, 2023
      I'm a newbie to VBA so pardon my ignorance.
      I like your VBA code because it looks like it would give dialog to verify deletion before activating, which is what I want to do, but after I copy code into VBA editor it doesn't run. Much of the code shows red (lines 1, 3-6, 15,16), is that indication of an error?

      Also, I want to only delete unprotected values on Active Sheet not entire Workbook - if I understand it, this code appears to clear entire workbook. I created a single button to activate the code as described in my comment for Rajesh_Sinha method shown below, but when I try to assign macro to button this macro does not show up on list. Thanks in advance for any advice.

Resources