Clearing Everything Except Formulas

I'm not able to make this work on anything but plan numbers. Is there another way to do this for date formulas? I built a production scheduling tool and would like to remove all the dates but leave the formulas so people can start with a clean slate instead of placeholder dates.

Pao

.ClearContents does not alter formats or validation

Mitchell

Why not name the data area 'Input' and then just delete the old values

How to clear without clearing a formats and data validation?

Can you clear everything but the first row that are headers and keep all formulas

You probably know this already, but Step 2 of your instructions shows "Comments" selected, not "Constants".

Great help. Thanks

How to possible that

sum formula worked but not delete formula or no edditing/correction in formula

I would also appreciate advice if there is a possibility to delete only unprotected cells. Thanks a lot! Would make my life a lot easier.

I used this method (F5 GoTO, Select "Constants", Unselect "text, the DEL) - but the application freezes and reboots, taking its time to do so. I am using Excel 2016.

Any advice?

Good afternoon;
Is there any means to remove values in ALL UNLOCKED cells within a protected worksheet? If you remove all but formulas you'll get rid of all cells contents that might contain names and/or other complementary information which you do not want to eliminate.
Thanks in advance...

Is there any means to remove values in ALL UNPROTECTED cells only? If you remove all but formulas you'll get rid of all cells that might contain names and/or other complementary information which you do not want to eliminate.
Thanks in advance...

@David

The above macro should work fine. Have you allowed macro execution in Excel options? Are you using .xlsm or .xls files instead of .xlsx ?

I have the same problem as Malika.

Can anyone advise.

THANK YOU!!!!!!

Good Afternoon,

Is there a way to remove the values of an Excel spreadsheet but keep the formulas? I have a 12 page Excel spreadsheet that I need to clear for the year 2016 but when I follow the instructions I found online, it erases the values and all the formulas I have saved. HELP!!
Thank you!

To address Wiily's cautionary final note in his 20 Jan post, when I do something like this, I typically add a msgbox with a warning and the ability to cancel the operation. That has saved me a few times...

@Willy

Hmmm, seems you are right. I exclusively use On Error GoTo so I thought the On Error Resume behaved the same way.

@balthamossa2b

Nope, you're wrong. After an 'On Error Resume Next', all runtime errors will be ignored until the procedure ends or an 'On Error GoTo 0' is met. A call to another procedure will suspend it during that call.

@Willy

On Error should be inside the loop. Say you have multiple sheets with no constants on them; the error will be triggered multiple times.

However once an On Error has been triggered (or the Resume instruction executed, in case you are using an On Error GoTo), you need to reload it to work; which means the second sheets without constants will give you an execution error.

There is no need to set 'On Error Resume Next' on and off for each worksheet. Just place it before the For Next statement and leave out 'On Error GoTo 0'.

Also 'Set wks = nothing' is unnecessary because it's the last statement of the macro and when it finishes the memory used by wks is freed anyway.

The 23 can also be left out because it's the default. The macro can thus be rewritten like this:

Sub ClearAllButFormulas()
Dim wks As Worksheet
On Error Resume Next 'ignore errors in case there is only formulas
For Each wks In Worksheets
wks.Cells.SpecialCells(xlCellTypeConstants).ClearContents
Next
End Sub

One final note: this is a very dangerous macro because when you run it accidentally in any workbook, everything except formulas in the entire workbook is gone. So, unless you know exactly what you are doing, the manual procedure described in this tip is preferred. You can always undo it when you are not satisfied with the result which is not the case when a macro does it.

Couple questions about the macro:

1) What does the On Error GoTo 0 do? All errors will be caught by Resume Next, I don't see when this one would be triggered.

2) What is the 23 for? By checking the function on VBA I see that it's an optional argument called Value, but I don't see what it does.

This tip shows how Excel saves time for the familiars.