Macro to clear Values Only

[howard]

I have tried to write code to clear Non-Formulas, but also do not want any tect cleared i.e I only want Values clear on sheet ("JNLS")

Kindly amend my code below

Sub ClearDataNonFormulas()
With Sheets("INLS")

With CreateObject("VBScript.RegExp")
On Error Resume Next

Sheets(1).UsedRange.SpecialCells(xlCellTypeConstants, 1).ClearContents
     End With
End Sub

 

[Colo]

Do you mean that you don't want to clear any Texts being shown by Formulas, but would like to clear all the Values? My idea is like this. As the first step, preserving ranges has Values using the "Range.SpecialCells" method. Then after converting the results of formulas to Values, Clear preserved ranges.

Sub test()
Sub ClearDataNonFormulas()
    Dim rngPreserved As Range
    With Sheets("INLS")
        Set rngPreserved = .UsedRange.SpecialCells(xlCellTypeConstants, 1)
        .UsedRange.Value = .UsedRange.Value
        rngPreserved.Clear
    End With
End Sub

 

[howard]

Thanks for the reply

My apologies. I only want to clear values but no formulas

 

[Jaafar Tribak]

Is this what you want ?
Caution: Try the code on a mock worksheet first because it will delete the numeric cells contents and you won't be able to undo it.

On Error Resume Next
Sheets("JNLS").UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

 

[howard]

Thanks Jafaar

Your code works perfectly