Delete content but not formulas

J

Jake F

I was asked if there is a way to remove the content of spreadsheets but to
keep the formulas. I'm not sure why the person wants to do this, but want to
give them a correct answer. Thanks.
 
T

Tim Zych

To remove values only from Excel:
Select all cells on the worksheet, or just one cell.
Press F5 (or Ctrl + G or Edit -> Goto)
Click the "Special" button.
Click "Constants"
Numbers, Text, Logicals, Errors should be checked.
Click OK.
Press Del (or Edit -> Clear -> Contents)
Repeat for other worksheets as needed.

Or use a macro -- for just the active sheet:
Sub RemoveValuesOnly()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange.Cells
If Not cell.HasFormula Then
cell.ClearContents
End If
Next
End Sub

Or for every worksheet in the active workbook:
Sub RemoveValuesOnly()
Dim cell As Range, wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
For Each cell In wks.UsedRange.Cells
If Not cell.HasFormula Then
cell.ClearContents
End If
Next
Next
End Sub
 
B

Bernard Liengme

Use Edit| GoTo Special; specify Constants and tap the Delete key
best wishes
 
Top