Allow ROW deletion but only below a specified range

Hello,

I am making a budgetary forecast worksheet and I'm hoping someone will know how to do something that I can't.

How can I either:

• Force the person to select the cell in column B for any ROW they want to delete

OR

• Specify a range that only allows rows below it to be deleted

Here is what I've been working with.

Please Login or Register  to view this content.

The problem is that I have two unlocked cells in row 3 that a person can select and fill from Data Validation Lists to produce monthly and annual expenditures in cells, also located in row 3, which all comes from data in rows from 7 down. If the person happens to have either one of those two cells selected at the time when they run the row deletion macro, it unlocks the sheet and allows that row to go ahead and be deleted, which I don't want to happen, even by accident. I only want them to be able to delete a row if it is below row 6.

Is this even possible?

Thanks very much for your help!

Tony,

From your explanation I am concluding that the user can only delete a row via the above macro, and not via right-clicking the row or choosing delete from the menu, right? If that is the case, would it then not be sufficient to check whether the row number of the 'Selection' object is below row 6?

Hello again, Ollie!

Correct, I'm not allowing them to delete rows unless they use the macro button. Yes, that would work. I assume it would involve some sort of loop with a message letting them know they must choose a row below row 6 and clicking OK would cause the process to stop.

Try

Please Login or Register  to view this content.

Hi Ollie,

Your example helped.

This is what I ended up with.

Please Login or Register  to view this content.

Without the else statement and moving things around a bit, it just kept popping up the message box but then didn't delete the row. It works perfectly with the code above.

Thanks again for your help, Ollie. Very much appreciated!!

Hi Beachrock,

In essence that is the same solution. Whether you use end if or else after the exit sub does not matter. Glad it works!