Macro to clear contents of unprotected cells AND drop down boxes

Hi there


I have the following macro which clears the contents of unprotected cells on
a sheet. It stops with the error message "400", however, when it gets to the
drop down boxes on the sheet within the selection.

Here is the macro:



Sub clearunprotectedcells()

Dim cell As Range
Range("A3:I59").Select
For Each cell In Selection
If cell.Locked = False Then
cell.ClearContents
End If
Next
End Sub


Does anybody have a way of modifying the script so it will clear all the
drop down boxes (they are all unprotected, incidentally!) AS WELL AS the
unprotected cells?


any help gratefully recieved. please do not hesitate to contact if you need
more info


cheers


jb

Hi there


as well as the request below (!), if anyone has any bright ideas on how i
can get it to clear Merged Cells as well, that would be great.

The "400" warning is coming up over those aswell...


Many thanks again


jb

"JB2010" wrote:

> Hi there
>
>
> I have the following macro which clears the contents of unprotected cells on
> a sheet. It stops with the error message "400", however, when it gets to the
> drop down boxes on the sheet within the selection.
>
> Here is the macro:
>
>
>
> Sub clearunprotectedcells()
>
> Dim cell As Range
> Range("A3:I59").Select
> For Each cell In Selection
> If cell.Locked = False Then
> cell.ClearContents
> End If
> Next
> End Sub
>
>
> Does anybody have a way of modifying the script so it will clear all the
> drop down boxes (they are all unprotected, incidentally!) AS WELL AS the
> unprotected cells?
>
>
> any help gratefully recieved. please do not hesitate to contact if you need
> more info
>
>
> cheers
>
>
> jb
>

RE the merged cells you could do this:

Sub a()
Dim Cell As Range
For Each Cell In Range("A3:I59")
If Cell.Locked = False Then
Cell.MergeArea.ClearContents
End If
Next
End Sub

I don't understand the other issue.

--
Jim
"JB2010" <[email protected]> wrote in message
news:[email protected]...
| Hi there
|
|
| as well as the request below (!), if anyone has any bright ideas on how i
| can get it to clear Merged Cells as well, that would be great.
|
| The "400" warning is coming up over those aswell...
|
|
| Many thanks again
|
|
| jb
|
| "JB2010" wrote:
|
| > Hi there
| >
| >
| > I have the following macro which clears the contents of unprotected
cells on
| > a sheet. It stops with the error message "400", however, when it gets to
the
| > drop down boxes on the sheet within the selection.
| >
| > Here is the macro:
| >
| >
| >
| > Sub clearunprotectedcells()
| >
| > Dim cell As Range
| > Range("A3:I59").Select
| > For Each cell In Selection
| > If cell.Locked = False Then
| > cell.ClearContents
| > End If
| > Next
| > End Sub
| >
| >
| > Does anybody have a way of modifying the script so it will clear all the
| > drop down boxes (they are all unprotected, incidentally!) AS WELL AS the
| > unprotected cells?
| >
| >
| > any help gratefully recieved. please do not hesitate to contact if you
need
| > more info
| >
| >
| > cheers
| >
| >
| > jb
| >

Jim


you're a legend.


many thanks for your help


the "400" thing comes up because of the merged cells, not the drop downs (i
had drop downs that were in merged cells & i originally mis-identified the
cause of the problem). All sorted now.


its so simple when you know how


apologies for being a luddite


cheers


jb

"Jim Rech" wrote:

> RE the merged cells you could do this:
>
> Sub a()
> Dim Cell As Range
> For Each Cell In Range("A3:I59")
> If Cell.Locked = False Then
> Cell.MergeArea.ClearContents
> End If
> Next
> End Sub
>
> I don't understand the other issue.
>
> --
> Jim
> "JB2010" <[email protected]> wrote in message
> news:[email protected]...
> | Hi there
> |
> |
> | as well as the request below (!), if anyone has any bright ideas on how i
> | can get it to clear Merged Cells as well, that would be great.
> |
> | The "400" warning is coming up over those aswell...
> |
> |
> | Many thanks again
> |
> |
> | jb
> |
> | "JB2010" wrote:
> |
> | > Hi there
> | >
> | >
> | > I have the following macro which clears the contents of unprotected
> cells on
> | > a sheet. It stops with the error message "400", however, when it gets to
> the
> | > drop down boxes on the sheet within the selection.
> | >
> | > Here is the macro:
> | >
> | >
> | >
> | > Sub clearunprotectedcells()
> | >
> | > Dim cell As Range
> | > Range("A3:I59").Select
> | > For Each cell In Selection
> | > If cell.Locked = False Then
> | > cell.ClearContents
> | > End If
> | > Next
> | > End Sub
> | >
> | >
> | > Does anybody have a way of modifying the script so it will clear all the
> | > drop down boxes (they are all unprotected, incidentally!) AS WELL AS the
> | > unprotected cells?
> | >
> | >
> | > any help gratefully recieved. please do not hesitate to contact if you
> need
> | > more info
> | >
> | >
> | > cheers
> | >
> | >
> | > jb
> | >
>
>
>