Deleting all unprotected cells

[mahmed1]

Hi Guys,

I have a workbook that is protected and locked however certain cells are unlocked and are not protected (I.e where i can enter values) etc...

I am trying to add a btton that will clear the form (the values in the unprotected/locked cells).

What is the VBA code to delete all values in the unprotected/locked cells in the workbook?

Many Thanks

 

[doofusboy]

Wouldn't something as simple as this work for you?

Dim r As Range
 
    For Each r In ActiveSheet.UsedRange
        If r.Locked = False Then
            r.ClearContents
        End If
    Next r

 

[MrKowz]

Try the following. It assumes that you have data extending to the last row in column A, and data extending to the last column in row 1:

Public Sub ClearUnlockedCells()
Dim rng As Range, _
    LR  As Long, _
    LC  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("A" & rows.Count).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).column
For Each rng In Range(Cells(1, 1), Cells(LR, LC))
    If rng.Locked = False Then
        rng.ClearContents
    End If
Next rng
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[mahmed1]

Hi All,

Thank you for all your help

I think i haven't explained myself properly

I have several of sheets which are locked and several of cells that are not locked accross all sheets.

I am running the code via a button on only one of the sheets. What i need is to clear all values in the workbook which are not locked. The code provided only clears data in the active sheet.

Hope this makes sense

 

[MrKowz]

Try:

Public Sub ClearUnlockedCells()
Dim rng As Range, _
    ws  As Worksheet, _
    LR  As Long, _
    LC  As Long
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For Each ws In ActiveWorkbook.Worksheets
    LR = ws.Range("A" & rows.Count).End(xlUp).Row
    LC = ws.Cells(1, Columns.Count).End(xlToLeft).column
 
    For Each rng In ws.Range(Cells(1, 1), Cells(LR, LC))
        If rng.Locked = False Then
            rng.ClearContents
        End If
    Next rng
Next ws
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[doofusboy]

Dim ws As Worksheet
Dim r As Range
 
    For Each ws In Worksheets
        ws.Unprotect
            For Each r In ActiveSheet.UsedRange
                If r.Locked = False Then
                    r.ClearContents
                End If
            Next r
        ws.Protect
    Next ws

 

[mahmed1]

Hi,

Thank you all for your help

I need a msgbox like this, where shall i add this in the code?


If MsgBox("Are you sure you want to clear the form?", vbYesNo + vbQuestion, "Warning...") <> vbYes Then Exit Sub</PRE><!-- / message --><!-- sig -->

 

[MrKowz]

Public Sub ClearUnlockedCells()
Dim rng As Range, _
    ws  As Worksheet, _
    LR  As Long, _
    LC  As Long
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
If MsgBox("Are you sure you want to clear the form?", vbYesNo + vbQuestion, "Warning...") <> vbYes Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
    LR = ws.Range("A" & rows.Count).End(xlUp).Row
    LC = ws.Cells(1, Columns.Count).End(xlToLeft).column
    
    For Each rng In ws.Range(Cells(1, 1), Cells(LR, LC))
        If rng.Locked = False Then
            rng.ClearContents
        End If
    Next rng
Next ws
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[mahmed1]

Public Sub ClearUnlockedCells()
Dim rng As Range, _
    ws  As Worksheet, _
    LR  As Long, _
    LC  As Long
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
If MsgBox("Are you sure you want to clear the form?", vbYesNo + vbQuestion, "Warning...") <> vbYes Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
    LR = ws.Range("A" & rows.Count).End(xlUp).Row
    LC = ws.Cells(1, Columns.Count).End(xlToLeft).column
 
    For Each rng In ws.Range(Cells(1, 1), Cells(LR, LC))
        If rng.Locked = False Then
            rng.ClearContents
        End If
    Next rng
Next ws
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Hi,

I get a method out of range error

Public Sub ClearUnlockedCells()
ActiveSheet.Unprotect Password:="Hello"
Dim rng As Range, _
ws As Worksheet, _
LR As Long, _
LC As Long

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
If MsgBox("Are you sure you want to clear the form?", vbYesNo + vbQuestion, "Warning...") <> vbYes Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
LC = ws.Cells(1, Columns.Count).End(xlToLeft).Column

For Each rng In ws.Range(Cells(1, 1), Cells(LR, LC))
If rng.Locked = False Then
rng.ClearContents
End If
Next rng
Next ws
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
ActiveSheet.Protect Password:="Hello", DrawingObjects:=True, Contents:=True, Userinterfaceonly:=True
End Sub

 

[Yard]

Just note that looping through each cell like this will cause each individual cell to be cleared individually.

Surely it is better to combine all relevant cells into a single range object and the clear the contents of that range object?

 

[mahmed1]

Just note that looping through each cell like this will cause each individual cell to be cleared individually.

Surely it is better to combine all relevant cells into a single range object and the clear the contents of that range object?

Hi, thank you for your response.

Will your code do that or will i need to amend it?