Clear all uprotected data?

[JoeBed]

Is there a way to clear all of the data you have entered on a
spreadsheet that is entered in the unprotected cells all at once?

 

[Damon Longworth]

Here is a routine to clear all unprotected cells:

Dim MyCell As Object

'clears all data from each unlocked cell

For Each MyCell In ActiveSheet.UsedRange

If MyCell.Locked = False Then

MyCell.ClearContents

End If

Next

 

[JoeBed]

Damon,
Thanks for responding to my question. I don't understand what you ar
saying. What is (Dim my cell as Object)? Is that part of the formula
What would the formula look like?
JoeBe

 

[anilsolipuram]

That is macro that Damon pasted

go to tools->macro->visual basic editor, insert module, paste the belo
code in the module, close the visual basic eidtor

go to tools->macro->macros->select "test_macro" and click run, i
should work now.


sub test_macro()
Dim MyCell As Object

'clears all data from each unlocked cell

For Each MyCell In ActiveSheet.UsedRange

If MyCell.Locked = False Then

MyCell.ClearContents

End If

Next
end su

 

[Dave Peterson]

Just to add to anilsolipuram's post.

If you've never writtin a macro before, you may want to look at David
McRitchie's site:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

=========
Another non-macro approach.

Give that range of cells that should be cleared a nice name.

Select the first cell and ctrl-click on subsequent cells.

Then Insert|Name|Define

Then when you want to clear those cells, you can hit Edit|Goto, type in that
name you created (hit enter) and then hit the delete key on the keyboard.

 

[JoeBed]

Anilsolipuram

I can get your Macro to work in a simple excell test but when I try it
in mine The test pops up (Cannot change part of a merged cell). The
Debug points at (MyCell.ClearContents) I have a lot of merged cells!
Any suggestions? Thanks for you help.
JoeBed

 

[Dave Peterson]

change
MyCell.ClearContents
to
MyCell.value = ""

 

[JoeBed]

anilsolipuram,

Thank you for your help. It works fine now.

JoeBed