Auto deleting using macros/vba

[Cash526]

Hello. To start I'm very new to using macros/vba. I created an excel sheet with locked and unlocked cells. I wanted to have a way to delete the contents of the unlocked cells so the user can start from scratch when the new year comes around. I got the code to delete all unlocked cells using this code in vba:

Sub SelectUnlockedCells()
Dim c As Range, r As Range
For Each c In ActiveSheet.UsedRange
    If c.Locked = False Then
        If r Is Nothing Then
            Set r = c
        Else
            Set r = Union(r, c)
        End If
    End If
Next
If r Is Nothing Then Exit Sub
r.Select
r.ClearContents
End Sub

What I can't figure out is how to prevent the deletion of 2 rows that are unlocked, more specifically from H4 to H53 & S4 to S53. So all other unlocked cells should be deleted except those. Any ideas would be greatly appreciated. PS I got the above code online. Thks.

 

[Malcolm]

Lock the required cells, then unlock them after clearing.

Sub SelectUnlockedCells()
    Dim c As Range, r As Range
    Dim TmpLock As Range


    Set TmpLock = Range("H4:H53,S4:S53")
    TmpLock.Locked = True
    For Each c In ActiveSheet.UsedRange
        If c.Locked = False Then
            If r Is Nothing Then
                Set r = c
            Else
                Set r = Union(r, c)
            End If
        End If
    Next
    If r Is Nothing Then
       TmpLock.Locked = False
       Exit Sub
    Else
       r.ClearContents
       TmpLock.Locked = False
    End If
End Sub

 

[Cash526]

Lock the required cells, then unlock them after clearing.

Sub SelectUnlockedCells()
    Dim c As Range, r As Range
    Dim TmpLock As Range


    Set TmpLock = Range("H4:H53,S4:S53")
    TmpLock.Locked = True
    For Each c In ActiveSheet.UsedRange
        If c.Locked = False Then
            If r Is Nothing Then
                Set r = c
            Else
                Set r = Union(r, c)
            End If
        End If
    Next
    If r Is Nothing Then
       TmpLock.Locked = False
       Exit Sub
    Else
       r.ClearContents
       TmpLock.Locked = False
    End If
End Sub

thks for the reply. I plugged the code in but I get an error. The debugger points to "templock.locked=true"

 

[Cash526]

The message I get is "unable to set the locked property of the Range class". Also it's "tmpLock.Locked = True"

 

[Malcolm]

Is your workbook/sheet protected?
Can you post a sample workbook?

 

[Cash526]

Is your workbook/sheet protected?
Can you post a sample workbook?

Malcolm, thanks for the response. I did protect the woork/sheet. I came up with a new strategy and recorded the macro for that. If you still would like to help me, I'm now trying to get a code so when I do run the macro I would like the drop down choice to move to the next selection on the list. It's actually a year selction. The list is from 2016 to 2050. So when I run the macro it will now clear the unprotected cells and change the year by 1. The list is located C100: C134. It's on my work computer and I'll try to post it my workbook here later on.