MACRO HELP!!! I Need A Macro That Will Clear Contents On All Unlocked Cells On A Sheet (excel 2010)

[Barryoffshore]

I have about 85 sheets in a workbook that are all very different in which cells are locked and which are unlocked. I need a macro that will "Clear Contents" on all unlocked cells on a sheet. The sheets are (or will be) protected. Can someone PLEASE help me!!!

 

[mikerickson]

Try this.
If any of the sheets are unprotected, it will kill everything on those sheets.
The .Protect line is there to prevent that.

Sub test()
    Dim oneSheet As Worksheet
    Application.DisplayAlerts = False
    On Error Resume Next
    For Each oneSheet In ThisWorkbook.Worksheets
        oneSheet.Protect
        oneSheet.Cells.Value = vbNullString
    Next oneSheet
    On Error GoTo 0
    Application.DisplayAlerts = True
End Sub

 

[shg]

Try this on a copy of the workbook:

Sub ClearUnlocked()
    Dim wks         As Worksheet

    On Error Resume Next
    For Each wks In Worksheets

        With wks
            If Not .ProtectContents Then
                .Protect
                .UsedRange.Value = ""
                .Unprotect
            Else
                .UsedRange.Value = ""
            End If
        End With
    Next wks
End Sub

 

[Barryoffshore]

Mr. mikerickson

THANK YOU VERY VERY MUCH!!! This worked to clear the workbook, which is also what I needed! You have a knowledge of excel that astounds me!!! Thank you again, very much!

 

[Barryoffshore]

shg - THANK YOU VERY MUCH!!! This works to clear each sheet, which is EXACTLY what I needed! I wish I had even a small amount of the knowledge you have! Thank you again, very much!

Try this on a copy of the workbook:

Sub ClearUnlocked()
    Dim wks         As Worksheet

    On Error Resume Next
    For Each wks In Worksheets

        With wks
            If Not .ProtectContents Then
                .Protect
                .UsedRange.Value = ""
                .Unprotect
            Else
                .UsedRange.Value = ""
            End If
        End With
    Next wks
End Sub

 

[Barryoffshore]

SHG & MIKERICKSON

Is there a macro that will clear ONLY the sheet I am in at the time? I thank y'all both very VERY much for all your help!!!

shg - THANK YOU VERY MUCH!!! This works to clear each sheet, which is EXACTLY what I needed! I wish I had even a small amount of the knowledge you have! Thank you again, very much!

 

[mikerickson]

Just eliminate the loop in the previous macro

Sub test()
   
    Application.DisplayAlerts = False
    On Error Resume Next
    
    ActiveSheet.Protect
    ActiveSheet.Cells.Value = vbNullString
    
    On Error GoTo 0
    Application.DisplayAlerts = True
End Sub

 

[Barryoffshore]

Mr. "Mikerickson", I can't thank you enough!!! You are just too smart - I wish I had just a fraction of your knowledge of excel!

Again... THANK YOU!!! THANK YOU!!! THANK YOU!!!

Just eliminate the loop in the previous macro

Sub test()
   
    Application.DisplayAlerts = False
    On Error Resume Next
    
    ActiveSheet.Protect
    ActiveSheet.Cells.Value = vbNullString
    
    On Error GoTo 0
    Application.DisplayAlerts = True
End Sub