how do i create a button to erase data but not formulas in EXCEL?
and not erase titles also
- 1 decade agoFavorite 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.
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
- 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.
- Anonymous1 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