Clearing unlocked cell contents macro

S

Steve

HYCH

Would like to run a macro with the following for several pages, but
only want to clear contents of unlocked cell within the worksheet,

rather than scrolling through and doing it manually?

any help with this would be wonderful

Steve
 
P

Per Jessen

Try this one. Note that the sheet has to be unprotected to use the
SpecialCells function.

Sub ClearUnlockedCells()
Dim sh As Worksheet
Dim cell As Range
Dim pWord As String

For Each sh In ThisWorkbook.Sheets
If sh.Name <> "NotThisSheet" Then
sh.Unprotect Password:=pWord
For Each cell In sh.Cells.SpecialCells(xlCellTypeConstants)
If cell.Locked = False Then
cell.Clear
End If
Next
sh.Protect Password:=pWord
End If
Next
End Sub

Regards,
Per
 
S

Steve

Try this one. Note that the sheet has to be unprotected to use the
SpecialCells function.

Sub ClearUnlockedCells()
Dim sh As Worksheet
Dim cell As Range
Dim pWord As String

For Each sh In ThisWorkbook.Sheets
    If sh.Name <> "NotThisSheet" Then
        sh.Unprotect Password:=pWord
        For Each cell In sh.Cells.SpecialCells(xlCellTypeConstants)
            If cell.Locked = False Then
                cell.Clear
            End If
        Next
        sh.Protect Password:=pWord
    End If
Next
End Sub

Regards,
Per



Per, thank you very much for your assistance, the sheets are
protected, but without password, so can cover this aspect with simply
unprotecting the sheet and re applying after this macro has run, have
22 worksheets, but only need to clear data in the first 7,

as i read your code please excuse me if i am wrong but this would
unprotect every sheet?

would i need to adjust the code to just unprotect Sheet1:Sheet7

Just some clarification

Again many thanks

Steve
 
S

Steve

Per, thank you very much for your assistance, the sheets are
protected, but without password, so can cover this aspect with simply
unprotecting the sheet and re applying after this macro has run, have
22 worksheets, but only need to clear data in the first 7,

as i read your code please excuse me if i am wrong but this would
unprotect every sheet?

would i need to adjust the code to just unprotect Sheet1:Sheet7

Just some clarification

Again many thanks

Steve- Hide quoted text -

- Show quoted text



have used the code below that was provided for me by pers, but am
getting an error message

Run time error '1004'
No cells were found


There are actually unlocked cells in the spreadsheets with information
in them that was expecting to be cleared

any help with this please
am using office 2003

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("w96").Value <> 0 Then
Dim sh As Worksheet
Dim cell As Range
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "wages sheet" Then
sh.Unprotect
For Each cell In sh.Cells.SpecialCells(xlCellTypeConstants)
If cell.Locked = False Then
cell.Clear
End If
Next
sh.Protect
End If
Next
End If
End Sub


Steve
 
P

Per Jessen

There has to be a sheet with no information to delete. I have added an
error check.

As you asked in your previous post, I have changed the loop, so the
macro loop through Sheet1:Sheet7

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("w96").Value <> 0 Then
Dim sh As Worksheet
Dim cell As Range
Dim c

For s = 1 To 7
Set sh = Worksheets("Sheet" & s)
sh.Unprotect
On Error Resume Next
c = sh.Cells.SpecialCells(xlCellTypeConstants).Count
If Err.Number = 0 Then
For Each cell In
sh.Cells.SpecialCells(xlCellTypeConstants)

If cell.Locked = False Then
cell.Clear
End If
Next
sh.Protect
End If
On Error GoTo 0
Next
End If
End Sub

Regards,
Per
 
S

Steve

There has to be a sheet with no information to delete. I have added an
error check.

As you asked in your previous post, I have changed the loop, so the
macro loop through Sheet1:Sheet7

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("w96").Value <> 0 Then
    Dim sh As Worksheet
    Dim cell As Range
    Dim c

    For s = 1 To 7
        Set sh = Worksheets("Sheet" & s)
        sh.Unprotect
        On Error Resume Next
        c = sh.Cells.SpecialCells(xlCellTypeConstants).Count
        If Err.Number = 0 Then
            For Each cell In
sh.Cells.SpecialCells(xlCellTypeConstants)

                If cell.Locked = False Then
                    cell.Clear
                End If
            Next
            sh.Protect
        End If
        On Error GoTo 0
    Next
End If
End Sub

Regards,
Per










- Show quoted text -

Per,

Your an absolute star, many thanks for your assistance

works perfectly

Steve
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top