locked cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mel - 05 Jul 2006 06:10 GMT

I have excel 2000, and have locked some cells on sheets to safeguard my
formulas whilst others are unlocked as they have weekly figures put in up
until Sunday. On Monday, a clean sheets starts again so I have to go and
delete all the entries on all the unlocked cells.

If I highlight the whole sheets and do edit, delete, it does not delete any
cells and gives the warning that I am trying to delete cells that are
locked. Is there any way to highlight the sheet and delete all the unlocked
cells in one go?
Thanks Mel

Reply to this Message

dodong - 05 Jul 2006 07:34 GMT

> I have excel 2000, and have locked some cells on sheets to safeguard my
> formulas whilst others are unlocked as they have weekly figures put in up
[quoted text clipped - 6 lines]
> cells in one go?
> Thanks Mel

Reply to this Message

dodong - 05 Jul 2006 07:36 GMT

Reply to this Message

dodong - 05 Jul 2006 07:43 GMT

Unprotect the file then hit F5 the dialog box will appear then choose
Specials then choose Constants then Delete, thats it.

Regards
Dodong

Reply to this Message

Mel - 05 Jul 2006 09:41 GMT

Thank you but when I press F5 it asks for a prin area? However this would be
slower in all as I would have to go to 18 sheets and unprotect every one
first clear the entries and then re protect. Is there no way to delete the
unprotected cells by highlighting the sheet and some form of delete?

> Unprotect the file then hit F5 the dialog box will appear then choose
> Specials then choose Constants then Delete, thats it.
>
> Regards
> Dodong

Reply to this Message

kounoike - 05 Jul 2006 09:48 GMT

If you don't mind using Macro, how about this one.

Sub clearunlockedconst()
Dim r As Range
On Error Resume Next
ActiveSheet.Unprotect Password:="" '<===change password to yours
For Each r In Cells.SpecialCells(xlCellTypeConstants)
If Not r.Locked Then
r.ClearContents
End If
Next
ActiveSheet.Protect Password:="" '<===change password to yours
End Sub

Reply to this Message

Mel - 05 Jul 2006 10:32 GMT

Now I only started to learn excel in the past 6 months and I went...dahhh!!
what is a Macro....then went l@@king, found it copied what you wrote had an
error and figured where it might be ( everything apart from expert help here
is trial and error, more error than trial)...had to figure that once created
then I go to Tools, Macro, run and I wondered " My god what will this do" as
I held my breath and pushed run.........................and all
gone.......what I wanted gone that is......your a genius.....

> If you don't mind using Macro, how about this one.
>
[quoted text clipped - 25 lines]
>> cells in one go?
>> Thanks Mel

Reply to this Message