Macro to clear unlocked cells

sduttonusa

New Member
Joined
Jan 13, 2016
Messages
43
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
 
Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If it is stored in your Personal workbook it should be ActiveWorkbook not ThisWorkbook

Code:
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
 
Last edited:

sduttonusa

New Member
Joined
Jan 13, 2016
Messages
43
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Code:
For Each xx In ActiveWorkbook.Worksheets
could be written as just
Code:
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

New Member
Joined
Jan 13, 2016
Messages
43
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:

Code:
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):

Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Code:
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
Code:
Sub ClearUnlockedCells()
    Dim Cell As Range
    For Each Cell In ActiveSheet.UsedRange
        If Cell.Locked = False Then Cell.Value = ""
    Next Cell
End Sub
:biggrin:

or as Rick Rothstein has previously posted
Code:
Sub ClearUnlockedCells()
  Application.FindFormat.Clear
  Application.FindFormat.Locked = False
  Cells.Replace "*", "", SearchFormat:=True, ReplaceFormat:=False
  Application.FindFormat.Clear
End Sub
which is more efficient
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,275
Messages
5,527,728
Members
409,785
Latest member
AdamPriest

This Week's Hot Topics

Top