Delete data in range of cells but retain the formula

Question

Is there a way to delete all the data in a range of cells but retain the formula. My example file is a simple calculation that will sum up numbers from C2:C5 and then put the sum in Sheets("Summary").range("B2"). After clicking the button, it will delete data in range A2:C5, however the formulas in cells A2:C5 will also be deleted.

My ideal result is that the formula in range(A2:C5) will retain.

Screenshot:

mycode:

    Private Sub CommandButton1_Click()


Dim sum As Worksheet, ws As Worksheet

Dim c As Range

Set sum = Sheets("Summary"): Set ws = Sheets("Sheet1")

sum.Range("B" & sum.Range("B1000000").End(xlUp).Offset(1, 0).Row) = WorksheetFunction.sum(ws.Range("C2:C" & ws.Range("C100000").End(xlUp).Row))
'ws.Range("A2:C" & ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row).ClearContents

For Each c In Sheet1.Range("A2:C9")
    If Not c.HasFormula Then c.ClearContents
Next c

End Sub

In your code, just don't delete anything in column C. Then in your formula in cells in C column, add an IF condition to check if there is a value in relevant row in column 'A' and 'B'. If it doesn't, just set the value in cell in 'C' column to nothing — Zac
Got it I just change something in my code. And whenever I hit the button, it will just delete cells A:B but not the C. — Kurt Matthew
Hi Zac, that helps a lot for such example of mine, however what if column A and B have formulas like =sumifs . That is something that I really wanted to fix.. — Kurt Matthew
I would suggest the same for columns 'A' and 'B' as well. Just have an IF condition in your formulas so they don't display anything is there is nothing to sum — Zac

Shai Rado Shai Rado · Accepted Answer · 2017-07-28T10:46:34

You could try something like the code below:

Dim C As Range
For Each C In Sheet1.Range("A2:C5")
    If Not C.HasFormula Then C.ClearContents
Next C

Delete data in range of cells but retain the formula

1 Answers