Sep 19 2020 11:06 AM
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.
I know I don't know anything (Socrates)
Sep 20 2020 03:30 AM - edited Sep 20 2020 03:36 AM
I would like to suggest two possible methods, one is the simplest through commands and another is the fastest VBA Macro.
All the unprotected cells will display in the list box.
Now you find all unlocked cells have been selected.
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