Forum Discussion
RLevkoy
Sep 19, 2020Copper Contributor
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...
NikolinoDE
Sep 19, 2020Gold Contributor
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
Feb 17, 2023Copper Contributor
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.
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.