[SOLVED] VBA delete Formula Sign from Excel

VBA delete Formula Sign from Excel

I am doing a validation at work and already did a lot of work, but now I am facing a problem. I need to delete the ' sign to clear unwanted Excel Formulas. I tried with Replace but it did not remove the '. Does anyone know how to delete it from a cell?

Solution

The apostrophe is kept hidden, not as part of the value so you cannot check for it via value or text or formula property. What you need to check for is the PrefixCharacter. Alas, that is read-only. Only way around this: clear the cell and re-set it. Short example that you should be able to implement:

Dim txt As String, r As Range

Set r = Cells(2, 1)  'contains apostrophe

If r.PrefixCharacter = "'" Then
    txt = r.Value
    r.Clear
    r.Value = txt
End If

Put this in an appropriate loop for your cell contents and you should be set.