How do I Clear ALL unprotected data entry fields in a protected sh

[Guest]

I have desinged a very simple Excel Worksheet for a friend of mine. She wants
to enter all the hours from a time card on each employee. It has two weeks of
data to enter so she can easily figure her payroll for each employee. It
looks something like this:

Column 1 Column 2 Colum 3 etc.
AM Time In Data Entry Same for next 6 employees
AM Time Out Data Out Same for next 6 employees
Total AM Protected Formula
PM Time In Data Entry Same for next 6 employees
PM Time out Data Entry Same for next 6 employees
Total PM Protected Formula
Totoal Hours Protected Formula

This is repeated for 2 weeks. All Fields are protected and locked except for
Data Entry Fields. My problem is, I would like to clear the sheet after the
payroll has been made and printed. If you have the work sheet protected, I
have no way to clear all the Data Entry fields at once. The only way I can do
it is to dlete each field individually as selecting all will not work on the
unproted Data Entry fields.

Please help. I have completed all the work except for this one item.

 

[Gary Keramidas]

select the cells and give them a range name, i just used data

then this will clear those cells

ActiveSheet.Range("data").Clear

 

[Guest]

With Gary's solution you will need to select a large number of individual
cells and then name the aggregate ( a bit of a nusance).
The following will ease the task.

First select the entire table (without any labels - just the data) and name
it, something like
TimeSheetData


Then use the following macro:

Option Explicit

Sub ClearData()
Range("TimeSheetData").SpecialCells(xlCellTypeConstants).ClearContents
End Sub

This will clear all non-formula cells.

 

[Gary Keramidas]

this wouldn't work for me with the sheet protected

 

[Guest]

sorry. try this

Sub ClearData()
ActiveSheet.Unprotect "Password"
Range("TimeSheetData").SpecialCells(xlCellTypeConstants).ClearContents
ActiveSheet.Protect "Password"
End Sub

 

[Tom Ogilvy]

for each cell in Range(B:T).SpecialCells(xlConstants)
if cell.locked = False then
cell.ClearContents
end if
Next