Thread: Deleting a protected row

I have an excel sheet which is protected. In this sheet there are some cells which are locked and some cells which are not. While protecting the excel sheet I have checked the option for allowing the users to delete rows. But still its not allowing me to delete the rows saying that you are trying to delet the cells which are locked.

How do I do this?

Hi reena,

You can't delete any range that contains a locked cell. You must remove the
sheet protection, delete the row, then restore protection. You can use a
macro to do the job, with very simple coding.

Try this:
Activesheet.Unprotect Password:="myPassword"
Selection.EntireRow.Delete
ActiveSheet.Protect Password:="myPassword", True, True, True, _
OtherOption1:=Option1Value, _
OtherOption2:=Option2Value, _
..and so on

Note that the options available for Excel2002 and later won't work in
earlier versions, so you'll have to modify the "ActiveSheet.Protect..." line
of code something like this if you want those features for later versions:

ActiveSheet.Protect Password:="myPassword", True, True, True
If Val(Application.Version) > 9 Then
OtherOption1:=Option1Value _
OtherOption2:=Option2Value _
'and so on
End If

Regards,
GS

"reena" wrote:

>
> I have an excel sheet which is protected. In this sheet there are some
> cells which are locked and some cells which are not. While protecting
> the excel sheet I have checked the option for allowing the users to
> delete rows. But still its not allowing me to delete the rows saying
> that you are trying to delet the cells which are locked.
>
> How do I do this?
>
>
> --
> reena
> ------------------------------------------------------------------------
> reena's Profile: http://www.excelforum.com/member.php...o&userid=30440
> View this thread: http://www.excelforum.com/showthread...hreadid=509805
>
>

Better:

Activesheet.Unprotect Password:="myPassword"
Selection.EntireRow.Delete
WksProtect()

Sub WksProtect()
With ActiveSheet
If val(Application.Version) >= 10 Then
.Protect " ", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
Userinterfaceonly:=True, _
AllowFiltering:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True
Else
.Protect " ", DrawingObjects:=True, Contents:=True, Scenarios:=True,
Userinterfaceonly:=True
End If
' .EnableSelection = 'optional parameters (xlNoRestrictions, OR
xlNoSelection, OR xlUnlockedCells) -choose only 1
End With
End Sub

Regards,
GS

Substitute .Protect " ", in the WksProtect() procedure example with
..Protect "myPassword", OR .Protect Password:="myPassword",

Regards,
GS