vba to clear cells but leave specific value

[dami916]

Hello,
Does anyone knows how to set VBA to clear a range but leave a specific value?

I was using this:

Sub ClearSheet()
' ClearSheet Macro


Range("B6:H25").Select
On Error GoTo Error_handler:

Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.ClearContents

Error_handler:
MsgBox "Cleared"

End Sub


but it only clears numbers. I need it to clear everything with exception of a specific value.

thank you

 

[dami916]

Sorry, I forgot about that. Give this a try then...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub DoNotContainClearCells()
  With Range("B5:H25")
    .Replace "OFF", "#N/A", xlWhole, , , , False, False
    .SpecialCells(xlConstants, xlNumbers + xlTextValues).ClearContents
    .SpecialCells(xlConstants).Value = "OFF"
  End With
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you, that works great