Macro to clear unlocked cells

[sduttonusa]

The past few years, I've used the following macro (which is stored in my PERSONAL xlb) to clear unlocked cells in my workbook:

Sub Clearem()
Dim xx As Worksheet
Dim yy As Range
For Each xx In ThisWorkbook.Worksheets
For Each yy In xx.UsedRange.Cells
If Not yy.Locked Then yy.ClearContents
Next
Next
End Sub

For some reason, it's not working anymore. I came across another macro and was wondering if it does the same exact thing:

Sub ClearUnlockedAllSheets()
Dim Cell As Range, Sht As Worksheet

For Each Sht In Worksheets
For Each Cell In Sht.UsedRange
If Cell.Locked = False Then Cell.Value = ""
Next Cell
Next Sht
End Sub

 

[MARK858]

If it is stored in your Personal workbook it should be ActiveWorkbook not ThisWorkbook

Sub Clearem()
    Dim xx As Worksheet
    Dim yy As Range
    For Each xx In [COLOR="#FF0000"]ActiveWorkbook[/COLOR].Worksheets
        For Each yy In xx.UsedRange.Cells
            If Not yy.Locked Then yy.ClearContents
        Next
    Next
End Sub

 

[sduttonusa]

Thank you so much for your quick reply. (So sorry about not properly posting my coding. I will pay attention to that in the future.) Yes, after reading your response, I realized that the last time I ran the first code, I did so outside my Personal Workbook. It was just recently that I put all my macros into my Personal Workbook.

The second macro code that I posted above does seem to work (clearing all unlocked cells) in the Personal Workbook . . . I was hoping to find out if the second coding does exactly the same thing?

 

[MARK858]

To all intent and purposes yes it does do the same thing although strictly speaking it is not clearing the cell rather changing it to an empty string but as far as I am aware doing it in VBA has the same result.

The reason the 2nd code works is if you don't refer to the workbook then Excel defaults to the ActiveWorkbook so in your first code

For Each xx In ActiveWorkbook.Worksheets

could be written as just

For Each xx In Worksheets

It should be Worksheets rather than Sheets as Sheets also includes Chart sheets (as well as Worksheets) so doing unnecessary checking.

 

[sduttonusa]

Thank you. The reason I was hoping the second coding did exactly the same thing had to do with the similar coding in both approaches to clearing unlocked cells in the active worksheet. With the first coding example, to clear the active worksheet, I had this:

Sub ClearUnlocked()
Dim xx As Worksheet
Dim yy As Range
Set xx=workbooks("workbookname").sheets("worksheetname")
For Each yy In xx.UsedRange.Cells
If Not yy.Locked Then yy.ClearContents
Next
End Sub

That required me to type in the exact name for the workbook and worksheet.

In the second coding example, that's not necessary (allowing me to create a macro for it):

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

Thank you so much for all your help!

 

[MARK858]

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

can just be written as

Sub ClearUnlockedCells()
    Dim Cell As Range
    For Each Cell In ActiveSheet.UsedRange
        If Cell.Locked = False Then Cell.Value = ""
    Next Cell
End Sub

or as Rick Rothstein has previously posted

Sub ClearUnlockedCells()
  Application.FindFormat.Clear
  Application.FindFormat.Locked = False
  Cells.Replace "*", "", SearchFormat:=True, ReplaceFormat:=False
  Application.FindFormat.Clear
End Sub

which is more efficient