Clear all uprotected data?
 

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?


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=380317

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

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?


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile:
http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=380317

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


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=380317

That is macro that Damon pasted

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

go to tools-macro-macros-select "test_macro" and click run, it
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 sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380317

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 wrote:

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

--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=380317

--

Dave Peterson

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


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=380317

change
MyCell.ClearContents
to
MyCell.value = ""



JoeBed wrote:

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

--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=380317

--

Dave Peterson

anilsolipuram,

Thank you for your help. It works fine now.

JoeBed


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=380317