Clear all uprotected data?

J

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?
 
D

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
 
J

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
 
A

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
 
D

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.
 
J

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
 
Top