Sep 19 2020 11:06 AM
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?
Sep 19 2020 12:19 PM
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)
Sep 20 2020 03:30 AM - edited Sep 20 2020 03:36 AM
@RLevkoy ,,,
I would like to suggest two possible methods, one is the simplest through commands and another is the fastest VBA Macro.
Method 1:
All the unprotected cells will display in the list box.
Now you find all unlocked cells have been selected.
Method 2:
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
Feb 17 2023 07:22 AM
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.
Feb 17 2023 10:55 AM
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
Feb 17 2023 11:30 AM
Feb 17 2023 11:36 AM