+ Reply to Thread
Results 1 to 7 of 7

clear contents cells of unprotected cells

  1. #1
    Ed
    Guest

    clear contents cells of unprotected cells

    Hi. I am trying to clear the contents of all unprotected cells only on a
    protected worksheet. Any ideas how to do it without giving the absolute cell
    reference for each of the unprotected cells I want to clear?

  2. #2
    Vasant Nanavati
    Guest

    Re: clear contents cells of unprotected cells

    Something like:

    Sub Test()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange.Cells
    If Not c.Locked Then c.ClearContents
    Next
    End Sub

    --

    Vasant

    "Ed" <[email protected]> wrote in message
    news:[email protected]...
    > Hi. I am trying to clear the contents of all unprotected cells only on a
    > protected worksheet. Any ideas how to do it without giving the absolute

    cell
    > reference for each of the unprotected cells I want to clear?




  3. #3
    Tom Ogilvy
    Guest

    Re: clear contents cells of unprotected cells

    for each cell in activesheet.UsedRange
    if cell.locked = false then
    cell.Clearcontents
    end if
    Next

    --
    Regards,
    Tom Ogilvy

    "Ed" <[email protected]> wrote in message
    news:[email protected]...
    > Hi. I am trying to clear the contents of all unprotected cells only on a
    > protected worksheet. Any ideas how to do it without giving the absolute

    cell
    > reference for each of the unprotected cells I want to clear?




  4. #4
    Ed
    Guest

    Re: clear contents cells of unprotected cells

    Thanks, it worked!

    "Vasant Nanavati" wrote:

    > Something like:
    >
    > Sub Test()
    > Dim c As Range
    > For Each c In ActiveSheet.UsedRange.Cells
    > If Not c.Locked Then c.ClearContents
    > Next
    > End Sub
    >
    > --
    >
    > Vasant
    >
    > "Ed" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi. I am trying to clear the contents of all unprotected cells only on a
    > > protected worksheet. Any ideas how to do it without giving the absolute

    > cell
    > > reference for each of the unprotected cells I want to clear?

    >
    >
    >


  5. #5
    Ed
    Guest

    Re: clear contents cells of unprotected cells

    Thanks, that did it!

    "Tom Ogilvy" wrote:

    > for each cell in activesheet.UsedRange
    > if cell.locked = false then
    > cell.Clearcontents
    > end if
    > Next
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Ed" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi. I am trying to clear the contents of all unprotected cells only on a
    > > protected worksheet. Any ideas how to do it without giving the absolute

    > cell
    > > reference for each of the unprotected cells I want to clear?

    >
    >
    >


  6. #6
    RemySS
    Guest

    Re: clear contents cells of unprotected cells

    Tom,

    I've implemented the code you supplied for Ed, however, some of the cells
    involved are merged and Error 1004 appears and says "Cannot change part of a
    merged cell". Any ideas?

    Thanks in advance!

    "Tom Ogilvy" wrote:

    > for each cell in activesheet.UsedRange
    > if cell.locked = false then
    > cell.Clearcontents
    > end if
    > Next
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Ed" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi. I am trying to clear the contents of all unprotected cells only on a
    > > protected worksheet. Any ideas how to do it without giving the absolute

    > cell
    > > reference for each of the unprotected cells I want to clear?

    >
    >
    >


  7. #7
    Dave Peterson
    Guest

    Re: clear contents cells of unprotected cells

    change:

    cell.Clearcontents
    to
    cell.value = ""



    RemySS wrote:
    >
    > Tom,
    >
    > I've implemented the code you supplied for Ed, however, some of the cells
    > involved are merged and Error 1004 appears and says "Cannot change part of a
    > merged cell". Any ideas?
    >
    > Thanks in advance!
    >
    > "Tom Ogilvy" wrote:
    >
    > > for each cell in activesheet.UsedRange
    > > if cell.locked = false then
    > > cell.Clearcontents
    > > end if
    > > Next
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > > "Ed" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Hi. I am trying to clear the contents of all unprotected cells only on a
    > > > protected worksheet. Any ideas how to do it without giving the absolute

    > > cell
    > > > reference for each of the unprotected cells I want to clear?

    > >
    > >
    > >


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1