promotion image of download ymail app

how do i create a button to erase data but not formulas in EXCEL?


and not erase titles also

3 Answers

  • 1 decade ago
    Favorite Answer


    Here is an example of a code snippet that will clear every cell

    in the used range that does not have a formula within the cell.

    Sub EraseData()

    Dim rng As Range, cell As Range

    Set rng = ActiveSheet.UsedRange

    Application.ScreenUpdating = False

    For Each cell In rng

    If Not cell.HasFormula Then cell.ClearContents


    Application.ScreenUpdating = True

    Set rng = Nothing

    End Sub

    • Commenter avatarLogin to reply the answers
  • 1 decade ago

    Best way is to turn on the Macro recorder, then manually erase the data, but not the formulas. Then save the macro and create a short cut for it.

    • Commenter avatarLogin to reply the answers
  • Anonymous
    1 decade ago

    - First create the button

    - Then Assign it to a macro (Enter the name even if it is not created yet)

    - Now go to VBE by Alt + F11

    - In a module add your sub with the name specified in Assign Macro

    - Paste this line into it

    Cells.SpecialCells( xlCellTypeConstants, 23).ClearContents

    - From now on clicking this button will delete all constant cells (Not formulas) in your active sheet

    Read my profile, I am XLMan

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.