Clearing Unlocked Cells in Excel

A macro makes it easy

People always ask, how to do you lock Excel? In fact you have to unlock Excel and then protect the sheet. The default setting for all cells is locked, so you need to unlock input cells.

I covered this topic in a recent post.

If you have unlocked your input cells you may want to clear them all and get them ready for input.

[Check out the comments below for a suggestion by Rick Rothstein an Excel MVP with an elegant technique.]

A macro can go through all the unlocked cells and remove any entries. The assumption here is that only the input cells are unlocked. I have included two macros. One for the current sheet and one for the whole file. Before we go through them let’s look at two other useful macros.

All four macros are in a file that you can download at the button at the bottom of this post.

But first a warning.

MACROS CLEAR THE UNDO LIST. THIS MEANS YOU CAN’T UNDO THE MACRO OR ANYTHING YOU DID BEFORE YOU RAN THE MACRO. IT IS ALWAYS A GOOD IDEA TO SAVE THE FILE BEFORE YOU RUN THE MACRO. IF SOMETHING BAD HAPPENS THEN CLOSE THE FILE WITHOUT SAVING.

First check your unlocked cells

You may have accidentally unlocked some non-input cells (Eg headings, constants or formulas) in the sheet. I have included two macros to check and/or report on all the unlocked cells. You can run these macros before clearing any unlocked input cells.

The first macro works on the current sheet and is used if you don’t have many input cells.

Message Box macro

If you think there may only be a few unlocked cells with entries this macro will display a message box for each unlocked cell that has an entry. If there are a lot this will take some time and then you can use the next macro.

Sub CheckUnLockedCells()
'This macro uses message boxes to displays details of unlocked cells
' that have values in the current sheet
 
Dim c As Range
Dim i As Long
 
On Error GoTo HandleError
 
For Each c In ActiveSheet.UsedRange
 
  If c.Locked = False And (c.Value <> "" Or c.Formula <> "") Then
 
    MsgBox ActiveSheet.Name & " " & c.Address & " has " & c.Value
 
    i = i + 1
 
  End If
 
Next c
 
'final message with how many unlocked cell have entries
If i = 0 Then
 
  MsgBox ActiveSheet.Name & " - all unlocked cells were empty."
 
Else
 
  MsgBox ActiveSheet.Name & " - has " & i & " unlocked cell(s) with entries."
 
End If
 
HandleExit:
  Set c = Nothing
  Exit Sub
 
HandleError:
  MsgBox "An error has occurred - macro stopped"
  GoTo HandleExit
 
End Sub

 

Unlocked cell report macro

This macro works on the whole file. It creates a table report of all the unlocked cell with entries, including a formula if they have one. You may need to lock some of these formula cells if they aren’t input cells. You can re-run this macro to see any differences.

Note: If you have the Microsoft Inquire add-in, it has a similar report.

Sub UnLockedCellsReport()
'this macro reports on all the unlocked cells in the file that have values
 
Dim c As Range
Dim ws As Worksheet
Dim wsRep As Worksheet
Dim r As Long
Dim strName As String
 
On Error GoTo HandleError
 
'name used for report sheet
strName = "_Unlocked_Cells_"
 
'see if the report sheet already exists
For Each ws In Worksheets
 
  If ws.Name = strName Then
 
   Set wsRep = ws
   ws.Cells.Value = ""
   Exit For
 
  End If
 
Next ws
 
'create the report sheet if it doesn't exist
If wsRep Is Nothing Then
 
  Set wsRep = Sheets.Add
  wsRep.Name = strName
 
End If
 
r = 2
 
'sheet headings
wsRep.Range("A1:D1") = Array("Sheet Name", "Unlocked Cell", "Value", "Formula")
wsRep.Range("A1:D1").Font.Bold = True
 
For Each ws In Worksheets
 
  If ws.Name <> strName Then
 
    For Each c In ws.UsedRange
 
      If c.Locked = False And (c.Value <> "" Or c.Formula <> "") Then
 
        'populate the report
        wsRep.Cells(r, 1) = ws.Name
        wsRep.Cells(r, 2) = Replace(c.Address, "$", "")
        wsRep.Cells(r, 3) = c.Value
        If Left(c.Formula, 1) = "=" Then wsRep.Cells(r, 4) = "'" & c.Formula
        r = r + 1
 
      End If
 
    Next c
 
  End If
 
Next ws
 
wsRep.Range("A1:D1").EntireColumn.AutoFit
 
HandleExit:
  Set c = Nothing
  Set ws = Nothing
  Set wsRep = Nothing
 
  Exit Sub
 
HandleError:
  MsgBox "An error has occurred - macro stopped"
  GoTo HandleExit
 
End Sub

 

Clear your unlocked cells

Once you are confident that only input cells are unlocked and you want to clear them then you can run the following macro. Remember macros can’t be undone so save your file before running this.

Sub ClearUnLockedCells()
'this macro clears entries in unlocked cells
' in the active sheet
 
Dim c As Range
 
On Error GoTo HandleError
 
For Each c In ActiveSheet.UsedRange
 
  If c.Locked = False Then c.Value = ""
 
Next c
 
HandleExit:
  Exit Sub
 
HandleError:
  MsgBox "An error has occurred - macro stopped"
  GoTo HandleExit
 
End Sub

If you want to do this for the whole file use the macro below

Sub ClearUnLockedCellsFile()
'this macro clears entries in unlocked cells
' in all the sheet in the file
 
Dim ws As Worksheet
Dim c As Range
 
On Error GoTo HandleError
 
For Each ws In Worksheets
 
  For Each c In ws.UsedRange
 
    If c.Locked = False Then c.Value = ""
 
  Next c
 
Next ws
 
HandleExit:
  Set ws = Nothing
  Set c = Nothing
  Exit Sub
 
HandleError:
  MsgBox "An error has occurred - macro stopped"
  GoTo HandleExit
 
End Sub

 

Merged Cells

The reason I use.

c.Value = ""

Instead of

c.ClearContents

Is that Merged Cells can cause the macro to crash when you use ClearContents.

You can download a file with all the above code at the button below.

Download Example File

The macros are  all in the ModUnocked module – see below. Press Alt + F11 to see the macro window.

Related Posts

Posted on
Categories: Excel Blog, Macros

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Clearing Unlocked Cells in Excel”

  1. When I read this article, a thought came to mind. I’ll look at the single (active) sheet example, but you can wrap the code in a For..Next loop that iterates the sheets if you want to process them all (just make sure to have the Cells range reference the loops sheet variable). IF no cells on the sheet being processed have any fill in them, then you can use this non-looping macro to fill all the unlocked cells that have either a value or formula in them with a color of your choice instead of popping up a MessageBox for each one of them. Once done, you can select the cells and set the fill color to none. Here is the macro that I thought of to do this…

    Sub ColorUnLockedCells()
    With Application
    .FindFormat.Clear
    .FindFormat.Locked = False
    .ReplaceFormat.Clear
    .ReplaceFormat.Interior.Color = vbYellow
    Cells.Replace “*”, “”, xlWhole, , , , True, True
    .FindFormat.Clear
    .ReplaceFormat.Clear
    End With
    End Sub

    Reply

    • Hi Rick

      Thanks for your comment – I will add a note in the article mentioning it.

      I always find your solutions elegant with a minimum of code.

      My code relies on the for next and for each loop a lot.

      Thanks again.

      Regards
      Neale

      Reply