Clear cells in workbook while leaving Formulas and headers intact

R

Raedars

New Member
Joined
May 29, 2019
Messages
2
I currently use Excel spreadsheet to enter time sheets before I send them off to be processed. I currently use the current week time sheet (adding and deleting employees as needed) - and then after I enter the weekly time I save as the current week but use SAVE as new workbook and clear cells indivudually as required to use as a blank template for the following week.

Is there a way to Mass clear only some spreadsheet cells while leaving the cells with Pertinent Employee info along with formulas for calculations alone?

I tried to write a code to clear cell contents in specific ranges , however since the number of rows in my worksheet change constantly - i am deleting rows that I need and not deleting those that I dont.

Sample below :
i require all the cells in BLACK to remain unchanged and the cells in RED to be cleared. Is this possible? is there a code I would write to make this happen?
Note: Protecting sheet / cells seems to be ineffective so far.





[TABLE="width: 1980"]
<colgroup><col><col><col><col><col><col><col><col><col span="21"><col span="3"><col><col></colgroup><tbody>[TR]
[TD]EE[/TD]
[TD] [/TD]
[TD="colspan: 2"]EMPLOYEE NAME[/TD]
[TD]PAY RATE[/TD]
[TD]LAC CODE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]ER[/TD]
[TD]EOT[/TD]
[TD]EDT[/TD]
[TD]ER[/TD]
[TD]EOT[/TD]
[TD]EDT[/TD]
[TD]ER[/TD]
[TD]EOT[/TD]
[TD]EDT[/TD]
[TD]ER[/TD]
[TD]EOT[/TD]
[TD]EDT[/TD]
[TD]ER[/TD]
[TD]EOT[/TD]
[TD]EDT[/TD]
[TD]ER[/TD]
[TD]EOT[/TD]
[TD]EDT[/TD]
[TD]ER[/TD]
[TD]EOT[/TD]
[TD]EDT[/TD]
[TD]ER[/TD]
[TD]EOT[/TD]
[TD]EDT[/TD]
[TD]TOTAL[/TD]
[TD]$$[/TD]
[/TR]
[TR]
[TD]No#[/TD]
[TD]C/I[/TD]
[TD]LAST[/TD]
[TD]FIRST[/TD]
[TD]& UNION[/TD]
[TD]& LEVEL[/TD]
[TD]JOB #[/TD]
[TD]DIM CODE[/TD]
[TD]1[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]ER-1[/TD]
[TD]EOT - 1.5[/TD]
[TD]EDT-2[/TD]
[TD]HOURS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]B1314[/TD]
[TD]I[/TD]
[TD]BISHOP[/TD]
[TD]FRANK[/TD]
[TD] $ 30.00[/TD]
[TD] L1034 [/TD]
[TD]BISHOP[/TD]
[TD]EE- Sub T[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]10.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]10.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]8.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]9.50[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]10.00[/TD]
[TD]0.00[/TD]
[TD]17.50[/TD]
[TD]10.00[/TD]
[TD]20.00[/TD]
[TD]47.50[/TD]
[TD]LOA [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]51303949[/TD]
[TD]NTX-LOA[/TD]
[TD] [/TD]
[TD]10.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$585.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]61307451[/TD]
[TD]NTX-LOA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$341.25[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] -[/TD]
[TD]NTX-LOA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] -[/TD]
[TD]NTX-LOA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]- [/TD]
[TD]NTX-LOA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] TRAVEL [/TD]
[TD]JM[/TD]
[TD] [/TD]
[TD]GEN DIM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 3"]WK TOTAL HRS:[/TD]
[TD="align: right"]47.50[/TD]
[TD] INDUSTRIAL [/TD]
[TD] [/TD]
[TD]51303949[/TD]
[TD]PLC[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]19.00[/TD]
[TD]19.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]51303949[/TD]
[TD]PLC-SFJ[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]1.00[/TD]
[TD]1.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]61307451[/TD]
[TD]PLC-X[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9.50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10.00[/TD]
[TD] [/TD]
[TD]17.50[/TD]
[TD]10.00[/TD]
[TD]0.00[/TD]
[TD]27.50[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Do the cells in red font hold constants and all the others (except the headers) formulas? Are the headers all in the same row? If yes, which row is that?
 
Upvote 0
Here's a trick I learned. Put a conditional formula in the cells you want to routinely clear. After you highlight the values you will clear, click the Home tab, click Conditional Formatting drop down, then New Rule, then OK. That will apply a conditional formatting that does nothing to the formatting.

When you're ready to clear those cells, click the Home tab, in the Editing Group, click the Find & Select drop-down, then click Go To Special. In the dialog box that opens, select Conditional Formatting. All of the cells you want to clear are now selected (highlighted). Click the Clear drop down in the same group and select Clear Content. That should do it. If you add new employee rows, make sure to select a row that contains an existing employee, <ctrl>+<c> to copy, then select the row of the new employee and Paste Special Formats. That way, their data will contain the conditional formatting and will be selected with the Go To Special trick.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Clear cell contents of non highlighted cells in a range
Replies
1
Views
864
Serafin54
S
F
  • Question Question
Help with converting shift time to hours worked with lunch deductions
Replies
0
Views
1K
Frank94
F
antinora
  • Question Question
Loop Through List of Names returning data for each row
Replies
1
Views
569
netuser
N
J
  • Question Question
Clear Cells, Leave Formulas
Replies
1
Views
632
JonnieO
J
E
  • Question Question
Calculating annual leave
Replies
1
Views
540
KeepTrying
K

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top