UsedRange = VbNullString in Excel 2016 returns with error saying that cells are protected

[egemencoskun]

Hi,

I have a code that goes through various sheets within the workbook and clear unprotected cells. Which have been working perfectly fine until I upgraded to Excel 2016. Frustrating and annoying as could not find the reason.

Code is below;

Dim wks As Worksheet
    
    On Error Resume Next
    For Each wks In Worksheets
        Select Case wks.Name
            Case "Authorise", "ATM Rec", "PC Exp", "Token Ctrl", "Cash Bingo", "Bingo Express", "Col Day Rec", "Mach Move", "Float", "PR1", "National Bingo", "Bingo Plus", "Admission", "PR1 (F)"
               wks.UsedRange.Value = vbNullString
                
            Case "Data", "UserData", "HOME", "Blank", "Input", "Checks"
                ' do nothing
        End Select
    Next wks

Run Time Error: 1004
Application-Defined or Object Defined Error
And debug is showing below line

wks.UsedRange.Value = vbNullString

When I tried to actually select UsedRange in ActiveWorksheet then selection delete it says that I am trying to clear contents from protected cells.

I also tried if range locked test but that one failed too.

I am totally stuck.

 

[mumps]

When you say "protected", I assume that you mean that the sheet is protected. Put your code between these two lines :

Sub Test()
    ActiveSheet.Unprotect
    'your code here
    ActiveSheet.Protect
End Sub

This code assumes there is no password.

 

[RoryA]

It appears the the VB Editor in your 2016 installation is set to break on all errors, otherwise your On Error Resume Next would suppress the error. In the VB Editor, select Tools - Options - General and set it to either break on unhandled errors, or in Class modules.

 

[egemencoskun]

Thanks for the replies.

Apologies as did not make it clear.

***I disabled error handler to test why it was not clearing anything. With error handler it looks like macro runs but nothing gets cleared from any cells.

***It is sheet protection but the issue with unprotecting the sheet is that the the code will clear everything on the selected sheets. I only need unprotected cells cleared.

 

[egemencoskun]

Can anyone help?