delete all cells with no formula

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I am trying to create a macro that will empty my worksheet
of all its entered data. Leaving the formulas.

What I have come up with is below. It deletes and the
cells shift up. I don't want that. How do I get it to
not delete, just clear so my formulas. I want formatting
to stay and cells not to move so the formuals still work.


Thanks, Todd


Sub DeleteUnLocked()
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each cell In Selection
If cell.Locked = False Then cell.ClearContents
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
MsgBox "No more cells to check"
End Sub
 
A formula can't do this but try this

F5
Special...Constants
Ok
Press the Delete button
 
... It deletes and the
cells shift up. I don't want that.
Click to expand...

I may be wrong, but I don't see any code that actually Deletes anything and
then cause the cells to shift up. Are you sure it is this code that is
causing your problem?
Is the Workbook Protected? Perhaps change the code slightly and manually
step thru the code and observe what happens.

For Each cell In Selection.Cells
If Not cell.Locked Then cell.ClearContents
Next cell
 
Hi Todd,

You need VBA code to make that automatic!

Sub DeleteContentsExceptFormulasInSelection()
On Error Resume Next
If Selection.Cells.Count = 1 Then Exit Sub ' need to select many cells
Selection.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End Sub


Regards,

Daniel M.
 
Back
Top