ExcelBanter - Macro to clear contents of unprotected cells AND drop down boxes

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Macro to clear contents of unprotected cells AND drop down boxes (https://www.excelbanter.com/excel-discussion-misc-queries/80276-macro-clear-contents-unprotected-cells-drop-down-boxes.html)

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

Macro to clear contents of unprotected cells AND drop down boxes

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

Macro to clear contents of unprotected cells AND drop down boxes

Macro to clear contents of unprotected cells AND drop down box

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" wrote in message
...
| 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
|