How to Clear Data Only from Block of Cells in Excel 2007

[JARHTMD]

I have a worksheet with a row for each day of 2016. There are formulas, conditional formatting, etc. Now I want to create an empty 2017 version. I thought I'd only have to copy and (somehow) empty the contents (each day's data), but everything I try removes everything; contents, formulae, formatting (conditional & otherwise).

How do I remove only the data that is in the block?

 

[Marcelo Branco]

Maybe...

F5 > Special
pick Constants
Delete the selected cells

M.

 

[Perpa]

How do I remove only the data that is in the block?

Or perhaps...
Using Right-Click and 'Clear Contents' what remains is still formatted, and data validation is still there.
However, the formulae are removed along with the data values.
You can use cell protection to keep the formula cells, here's how:

First select the cells without formulae in them (the data cells). Select the first cell, and then press and hold the 'ctrl' key and left click all the other cells.
With all the data cells selected, right click and select 'Format Cells', then 'Protection'.
Remove the check mark in the block marked 'Locked', click 'OK'

Then go to the 'Review' on the top ribbon, and select 'Protect Sheet'.
The heading says 'Allow all users of this worksheet to:'
Uncheck the box that says 'Select locked cells'. Then click on 'OK'.

Now when you select all the cells, only the ones that are unprotected will clear when you use 'Clear Contents'.
That should do it.
Perpa

 

[Logit]

This is untested but from a reputable resource :

[COLOR=#000000]Sub ClearAllButFormulas()[/COLOR]    
'clear all cells / all sheets of contents except formulas
Dim wks As Worksheet

    For Each wks In Worksheets
        'ignore errors in case there is only formulas
        On Error Resume Next
        wks.Cells.SpecialCells _
          (xlCellTypeConstants, 23).ClearContents
        On Error GoTo 0
    Next
    Set wks = Nothing 
[COLOR=#000000]End Sub[/COLOR]

 

[JARHTMD]

Thanks to all for your replies. Sorry for my tardy acknowledgement. I successfully used Marcelo Branco's method. Saved all for future reference. Thanks again for your help.
I would mark this thread "solved", but don't know how.

 

[Marcelo Branco]

You are welcome. Glad to help

M.
ps: "Thanks to all" is enough to mark the thread as "solved".