Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Clear only unprotected cells

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    345
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need a macro to clear unprotected cells on a protected sheet. If it matters, the cells may contain values or text. Also, this is in Excel2002 which offers lots more options with respect to sheet protection. I tried creating a named range that includes every cell in the print area but once sheet protection is activated, the entire range can't be selected. I would even accept clearing every unprotected cell on the sheet. I'll just bet there's some simple code to do this.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there

    You could try something like this:

    For Each cell In Range("B4:J22")
    If cell.Locked = False Then
    cell.ClearContents
    End If
    Next
    End Sub

    Change the range to your range
    (works in Excel 2000)

    regards
    Derek


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    This will clear unlocked cells rather the
    worksheet is protected or not...
    Note:
    If you do not want to wait around,
    change this Line:

    For Each c In ActiveSheet.Cells

    to a bit smaller range such as:

    For Each c In range.("A1:Z1000")

    Sub ClearCells()
    Dim c
    For Each c In ActiveSheet.Cells
    If c.Locked = False Then _
    c.ClearContents
    Next
    End Sub

    You may also Unprotect a sheet via code at
    the beginning of you procedure,
    run your code, and then protect it again.

    ActiveSheet.Unprotect password:="YourPassword"
    'code here
    ActiveSheet.Protect password:="YourPassword"
    The password is optional...

    Tom

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Didn't mean to repeat you there Derek...

    Tom

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    345
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks guys, this will do it.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •