Re: Delete content but not formulas



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

--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions


"Jake F" <JakeF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8CAD182A-8868-4036-B576-47A1C6C8F8B5@xxxxxxxxxxxxxxxx
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.


.



Relevant Pages

  • Re: How to capture Max cell value (High Water Mark)
    ... Where A1 is the cell that is changing and the HiWater function can be in any ... I don't have any other macros in the workbook. ... Sub Test_Enable_Events ... I'm only using it on the same worksheet where the macro was created. ...
    (microsoft.public.excel)
  • Re: How to capture Max cell value (High Water Mark)
    ... directly addressing the changing cell. ... I don't have any other macros in the workbook. ... Sub Test_Enable_Events ... "Note that it only works with the worksheet under which it was ...
    (microsoft.public.excel)
  • RE: Cross Function when array crosses above another
    ... When I checked the simple sample array, it appeared to answer the question, ... Private Sub CommandButton1_Click ... 'Add the following arrays that are sourced from the worksheet: ... a cell where you want to start storing the data and then press OK. ...
    (microsoft.public.excel.programming)
  • Re: Passing excel objects to subroutines as parameters
    ... finds a specified cell value on an excel spreadsheet and then ... formats the found cell and inserts text next to the found cell. ... statement the code simply exits the sub routine and proceeds with ... What I go by when automating, I can play with the stuff in the ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to capture Max cell value (High Water Mark)
    ... a look saving the value and initializing it on worksheet open. ... addressing whatever cell was active on the worksheet. ... Sub Test_Enable_Events ... I'm only using it on the same worksheet where the macro was created. ...
    (microsoft.public.excel)