How to delete rows in a protected worksheet

[md95065]

I am using excel 2007.

I want to allow users to delete rows from a protected worksheet.

I understand that if there are any cells in the row that are locked then the user will not be able to delete the row. That is OK. In the rows that I want the user to be able to delete there do not need to be any locked cells.

But, no matter what I do I simply cannot find a way to delete any rows once the worksheet is protected.

Consider an extreme example - not particularly useful, but it demonstrates the point.

On a fresh worksheet select all possible cells and use "Format Cells ..." to mark them all as unlocked, then protect the worksheet specifying that all users are allowed to - select locked cells, select unlocked cells, insert rows and delete rows.

Now try to delete a row - you can't!

You can insert rows but you can't delete them.

The "Insert" menu shows "Insert Sheet Rows" and "Insert Sheet" as being available.

The "Delete" menu only has "Delete Sheet" - everything else is greyed out ...

Someone please tell me that I am being stupid and doing something wrong and that this isn't just another example of Excel being horribly broken

[VBA Noob]

Hi,

See link for info

http://exceltips.vitalnews.com/Pages...Worksheet.html

You could use this macro

Please Login or Register  to view this content.

VBA Noob

[md95065]

Hi,

See link for info

http://exceltips.vitalnews.com/Pages...Worksheet.html

Thanks for the link.

Actually I had already seen that article and it was what prompted me to ask the question here.

In the specific case that I am concerned about there are no locked cells in the row that I want to delete. To be clear about this - I select the cell in column A of that row and press ctrl shift right-arrow to select every cell in the row and I then set all of the cells to unlocked.

Despite this, when I protect the worksheet I cannot delete that row even though I specified that users should be able to delete rows.

So it seems to me that either I am still misunderstanding what is supposed to happen or else I am doing something wrong.

I realize that I could use a macro to do this, but right now I am still trying to figure out why the "obvious" solution doesn't work.

[VBA Noob]

I select the cell in column A of that row

Why select Col A. Surely it should be a Row ??

So the macro option isn't a option?

VBA Noob

[md95065]

Why select Col A. Surely it should be a Row ??

So the macro option isn't a option?

VBA Noob

What I said was:

I select the cell in column A of that row and press ctrl shift right-arrow to select every cell in the row and I then set all of the cells to unlocked.

I am not selecting Column A - I am selecting every cell in a particular row and making sure that it is not locked.

The macro is certainly an option, but I would like to make this template as functional as possible without macros.

[DrTyack]

Hi,

I had the same problem with this however the solution left me kicking myself.

I had no protected columns in my sheet but still could not delete any rows,

simply;

when the worksheet is unlocked highlight the rows by clicking on the row number (to the left of column A)

then right click and > format cells > protection > then deselect the 'lock' tick box

when you lock the sheet all those rows will be deletable as they are not locked.

the issue seems to be with the 'Allow users to edit ranges' function - where selecting whole rows does not allow users to delete them.

Hope this helps

[jmike47]

I didn't try the macro, but I tried the other advice and it didn't work for me, UNLESS I am doing something wrong. I am like the other person; I would rather not use a macro. Please advise.

[Paul]

Hello Jmike47. Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Thread is now closed.