Excel: ClearContent preserving formulas

Question

In Excel VBA is there a way to safely use Range("A1:B2").ClearContent without deleting the existent formulas in the cells?

Just to make my scenario clearer: I'm pulling fresh data from the database and just want to erase everything on the sheet but not the formulas because those will resolve some data in other fields with vlookups.

So ok, assume that you have some formatting on the cells for example you want a cell formatted as date and ClearContent is going to delete even that formatting. How would you prevent this? — Lorenzo, Dec 13 '10 at 16:20
Could you elaborate on what makes no sense in my question? I've tried to explain myself already — Lorenzo, Dec 13 '10 at 16:30
@SLacks Excel distinguish between formulas and constants. That is the main reason behind using "=" to enter formulas. And yes, you can select one or the other. See @Dick's answer — Dr. belisarius, Dec 13 '10 at 17:37

score 17 · Accepted Answer · answered Dec 13 '10 at 16:55

17

Use the SpecialCells property to get only the constant values.

Sub RemoveConstants()

    Dim rConstants As Range

    Set rConstants = Sheet1.Range("A1:B2").SpecialCells(xlCellTypeConstants)
    rConstants.ClearContents

End Sub

answered Dec 13 '10 at 16:55

Dick Kusleika

32,673
4
52
73

2

Note that if no cells in the range actually have values, Excel throws an error "No cells were found" in response to Set rConstants = Sheet1.Range("A1:B2").SpecialCells(xlCellTypeConstants) – Murrah Aug 05 '14 at 23:02
3

Add a `On Error Resume Next` at the start of the sub to avoid the "No cells were found" error. – Nikhil Gupta Jul 24 '15 at 10:24

Excel: ClearContent preserving formulas

1 Answers1