Trying to clear all unprotected cells in a workbook

[hnswart]

Hi all.

I am new to macros. I uses a macro to clear all unprotected cells in a workbook. It works perfectly in older versions. When i run the macro in 2016 it does nothing.

Here is the code:

Sub ClearUnlockedCells()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
On Error Resume Next
wks.UsedRange.Value = vbNullString
Err.Clear: On Error GoTo -1: On Error GoTo 0
Next wks

Set wks = Nothing

End Sub

Please help

 

[mikerickson]

I don't see the label -1 referred to by that On Error statement.

 

[hnswart]

I don't see the label -1 referred to by that On Error statement.

Sorry i dont understand. I got this code off a forum and it works perfectly well in older versions of excel.

 

[Fluff]

That code works for me with 365 & should work in 2016.
Is the code located in the workbook that you are trying to clear?

 

[Jon von der Heyden]

You could try:

Public Sub ClearUnlockedCells()
    Dim wks     As Excel.Worksheet
    Dim rng     As Excel.Range
    
    For Each wks In ThisWorkbook.Worksheets
        For Each rng In wks.UsedRange.Cells
            If Not rng.Locked Then rng.ClearContents
        Next rng
    Next wks
End Sub

 

[hnswart]

Public Sub ClearUnlockedCells() Dim wks As Excel.Worksheet Dim rng As Excel.Range For Each wks In ThisWorkbook.Worksheets For Each rng In wks.UsedRange.Cells If Not rng.Locked Then rng.ClearContents Next rng Next wks End Sub

Thank you for this code. I get the following error when i run the macro. Please see image

 

[hnswart]

That code works for me with 365 & should work in 2016.
Is the code located in the workbook that you are trying to clear?

Hi, yes it is in the correct workbook. It does nothing when i run the macro.