Delete the contents of all cells which dont contain formulae

[Warby]

Please can anyone help with a method of deletiong all values except formulae
from a range of cells in single and multiple worksheets?

 

[JE McGimpsey]

One way:

Choose Edit/Go To.../Special, select Constants from the dialog. Then
Edit/Clear All.

 

[Teethless mama]

First save your workbook.

Hit F5 > Special > select Constants > OK out > while highlight all values
press Delete key

 

[Gary''s Student]

Select your range of cells and run:

Sub clear_val()
For Each r In Selection
If r.HasFormula Then
Else
r.Clear
End If
Next
End Sub


repeat for other worksheets

 

[Dave Peterson]

Select the range first--be sure not to include the cells that contain labels
that you want to keep (headers/instructions).

Then edit|Goto|Special|Constants
and click ok.
The original selection will be reduced to just the cells that contain constants.
Then hit the delete key on the keyboard.

In code, you can loop through the worksheets like:

Option Explicit
Sub testme()
Dim wks As Worksheet

On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Cells.SpecialCells(xlCellTypeConstants).ClearContents
Next wks
On Error GoTo 0
End Sub

But this will clear the contents of all cells with values--including headers.
I'd be careful.