Clear all unlocked cells (protected sheets)

[keessieb]

In a workbook with several protected sheets, I would like to give an option for the user to clear all not protected cells. Just a button on sheet one what says "clear all". Can it be done? (all not protected cells are yellow, is there a way to say "clear all yellow cells?)"

Thanks in advance!

 

[gkairu]

Heres a version specific to your requirements. Note this only works on the current active sheet (not all sheets). If you dont know how to add a button or add the code post again

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.ClearContents
    Next Cell
End Sub

hth

Is it possible to have your code above insert a 0 (zero) instead of a blank when clearing? Am totally new to vba codes.

 

[gkairu]

Is it possible to have a button in excel that insert a 0 (zero) instead of a blank when clearing unlocked cells in a protected sheet? Am totally new to vba codes.

 

[Haree]

Hi @gkairu

Try this in a copy of your workbook as after running the Macro you won't get Undo option and I am a beginner in VBA.

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 = 0
    Next Cell
End Sub

P.S This code has been edited from @parry's Code
Hope this helps

 

[gkairu]

Hi @gkairu

Try this in a copy of your workbook as after running the Macro you won't get Undo option and I am a beginner in VBA.

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 = 0
    Next Cell
End Sub

P.S This code has been edited from @parry's Code
Hope this helps

Thanks, let me try it out.

 

[Fluff]

Another option

Sub gkairu()
   With Application.FindFormat
      .Clear
      .Locked = False
   End With
   ActiveSheet.UsedRange.Replace "*", 0, , , , , True, False
   Application.FindFormat.Clear
End Sub