protecting spreadsheet

[Guest]

I have a spreadsheet where the data in some rows needs to be changed weekly
and other rows in the spreadsheet stay the same. I've been deleting all of
the rows data and reentering the data that is the same from the previous week
which is very tedious. Is there a way to protect the data in the rows that
stay the same and delete the data that does not? I tried protecting the rows
and the worksheet and then I'm not able to quickly delete the data in the
rows that I need to change without selecting each row individually. How can I
quickly delete data in rows I want to and not the protected ones?

 

[Gord Dibben]

Jensen

Unlock the cells you want to delete under Format>Cells>Protection

Then Tools>Protection>Protect Sheet.

Add this macro to a general module in your workbook and run it.

Sub UnlockedCells()
Dim cl As Range
For Each cl In ActiveSheet.UsedRange
If cl.Locked = False Then
cl.ClearContents
End If
Next
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

I have a spreadsheet where the data in some rows needs to be changed weekly
and other rows in the spreadsheet stay the same. I've been deleting all of
the rows data and reentering the data that is the same from the previous week
which is very tedious. Is there a way to protect the data in the rows that
stay the same and delete the data that does not? I tried protecting the rows
and the worksheet and then I'm not able to quickly delete the data in the
rows that I need to change without selecting each row individually. How can I
quickly delete data in rows I want to and not the protected ones?

Gord Dibben MS Excel MVP