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.

Answer 1

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