Clearing all unlocked cells on every sheet in a workbook.

[jimbofoxman]

I have 3 bits of code in the PERSONAL workbook (hidden on start). First two bits unprotect and protect all worksheets. I have a work book with one tab for every day of the Quarter. On the code I got for clearing all unprotected/unlocked cells it's acting like it's not even running. Is it because the sheets are protected? Even though I have certain cells unlocked.

I modified my ribbon to have buttons for each 3 macros from the PERSONAL workbook.

Here is the code I currently have. I have tried several and it seems like it total ignores it, nothing gets cleared.

    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

Not sure why it acts like it's ignoring it. I've read in several posts about different code for merged cells. I do have some merged cells to deal with too.

Just kinda stumped.

Thanks

 

[dmt32]

Hi,
I suspect your code is throwing an error but masked by the Resume Next error trap you have.

solution untested but try following & see if does what you want

Sub ClearUnlockedAllSheets()
Dim Cell As Range, ClearUnlockedCells As Range
Dim Sht As Worksheet
  
For Each Sht In Worksheets
    For Each Cell In Sht.UsedRange.Cells
    If Not Cell.Locked Then
        If ClearUnlockedCells Is Nothing Then
            Set ClearUnlockedCells = Cell
        Else
            Set ClearUnlockedCells = Union(ClearUnlockedCells, Cell)
        End If
    End If
    Next Cell
    If Not ClearUnlockedCells Is Nothing Then ClearUnlockedCells.Clear
    Set ClearUnlockedCells = Nothing
Next Sht
End Sub

Dave

 

[jimbofoxman]

Thanks Dave! It worked great. Do you know if their is a way to just clear the data and not the formatting? I have the cells filled with colors so they enter data from the right spots.

Thanks again!

Jim

 

[MARK858]

Change clear to clearcontents.
Btw in your original code it should have been ActiveWorkbook.Worksheets not ThisWorkbook.Worksheets.

 

[dmt32]

Thanks Dave! It worked great. Do you know if their is a way to just clear the data and not the formatting? I have the cells filled with colors so they enter data from the right spots.

Thanks again!

Jim

welcome glad it helped

change part line shown in red

If Not ClearUnlockedCells Is Nothing Then ClearUnlockedCells.ClearContents

Dave

 

[jimbofoxman]

Perfect! Thanks Mark & Dave for the responses. Works great!