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...
excel
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
      • YvonneC1190's avatar
        YvonneC1190
        Copper Contributor
        Aug 27, 2024
        Thank you for this! The workbook I tried this on is a very large multiple sheet workbook and this sucessfully cleared my final data sheet while maintaining the formulas. I want to be able to clear all 6 sheets in the workbook, some have locked cells and other sheets are complete clears. I am new to VBA,can I designate each sheet (or combination of sheets) and combine this code for the sheets with locked cells in conjunction with your clear all code below for the sheets that have no loced cells so I have just one macro to run to clear the workbook. This is a biweely process and we need a fresh start each time we run the process. Thank you for your guidance.

Resources