Forum Discussion
How can I erase all non-locked cells in a protected worksheet with one keystroke
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.
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- 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.
I'm sorry, I do not understand. What exactly do you want to do with sheets that do not have locked cells?
HansVogelaar,
Thank you for your quick response. The formula you wrote references Set WorkRange = ActiveSheet.UsedRange; I want to have the formula run on several sheets in the workbook. With my limted knowledge of VBA, I have only ever used something like Sheets("combined all").Select first. Would this be the correct entry to use for each sheet I want and then plug your formula in after each sheet is selected? There are 6 sheets in the workbook and I need to clear 5, 2 have locked cells, 3 do not.
- I am new to working with VBA and would like to use this. I have a title page where I am creating a "CLEAR ALL" worksheets as well as specific pages within said workbook. How can I adjust this formula to do this?
To clear unlocked cells on ALL worksheets, you can use
Sub ClearAll() Dim ASheet As Worksheet Dim WorkRange As Range Dim UnlockedRange As Range Dim Cell As Range If MsgBox("Are you sure you want to clear unlocked cells on all sheets?", _ vbYesNo + vbQuestion) = vbYes Then For Each ASheet In Worksheets Set WorkRange = ASheet.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 Next ASheet End If End SubTo clear unlocked cells from specific sheets:
Sub ClearSpecific() Dim ASheet As Worksheet Dim WorkRange As Range Dim UnlockedRange As Range Dim Cell As Range If MsgBox("Are you sure you want to clear unlocked cells on specific sheets?", _ vbYesNo + vbQuestion) = vbYes Then ' Change the array as needed For Each ASheet In Worksheets(Array("ThisOne", "ThatOne", "AnotherOne")) Set WorkRange = ASheet.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 Next ASheet End If End Sub
- Perfect! Thank you much for your super quick help Hans!
