Clear only cells containing formulas

Question

I need a VBA code that clears only cells containing formulas and skip cells containing values in a given Excel Worksheet. I have the following Code:

Dim rng As Range, cl As Range
Set rng = ThisWorkbook.Sheets("MATRIX").Range("C2:AU10000")
    For Each cl In rng
        If cl.Hyperlinks = .Hyperlinks Then
            cl.ClearContents
        End If
    Next cl

Answer 1

Try this approach, please:

If cl.HasFormula Then
       cl.ClearContents
End If

Answer 2

This should be the quickest way

rng.SpecialCells(xlCellTypeFormulas).ClearContents

Answer 3

HasFormula vs SpecialCells(xlTypeCellTypeFormulas)

Option Explicit

Sub RemoveHasFormula()
    Dim rng As Range, cl As Range
    Set rng = ThisWorkbook.Sheets("MATRIX").Range("C2:AU10000")
    For Each cl In rng
        If cl.HasFormula Then cl.ClearContents
    Next cl
End Sub

Sub RemoveSpecialCells()
    Dim rng As Range
    Set rng = ThisWorkbook.Sheets("MATRIX").Range("C2:AU10000")
    rng.SpecialCells(xlCellTypeFormulas).ClearContents
End Sub

Answer 4

Delete formulae via a range's XML structure

For the sake of the art and in order to complete the above solutions I demonstrate a rather unknown approach using a range's xml spreadsheet value (so called ".Value(11)"):

Option Explicit

Sub ExampleCall()
    Dim rng As Range: Set rng = Sheet1.Range("a11:b14")
'a) Get range data as xml spreadsheet value
    Dim s As String: s = rng.value(xlRangeValueXMLSpreadsheet) ' //or: s = rng.Value(11)
'b) delete formulae and write data back to range
    ClearFormulae s                                            ' call sub changing s (By Reference)
    rng.value(xlRangeValueXMLSpreadsheet) = s
End Sub

Sub ClearFormulae(s)
'Purpose: delete formulae in xlRangeValueXMLSpreadsheet contents of a given range
'Author : https://stackoverflow.com/users/6460297/t-m
'Date   : 2020-07-18
'[1]Set xml document to memory
    Dim xDoc As Object: Set xDoc = CreateObject("MSXML2.DOMDocument.6.0")
'[2]Add namespaces.
    xDoc.SetProperty "SelectionNamespaces", _
        "xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' " & _
        "xmlns:ht='http://www.w3.org/TR/REC-html40'"

'[3]Load cells with formulae into xml document.
If xDoc.LoadXML(s) Then         ' load wellformed string content
    Dim cell As Object, cells As Object
    Set cells = xDoc.SelectNodes("//ss:Cell[@ss:Formula]")  ' XPath using namespace prefixes
    For Each cell In cells
        cell.RemoveAttribute ("ss:Formula")
        cell.SelectSingleNode("ss:Data/@ss:Type").Text = "String"
        cell.SelectSingleNode("ss:Data").Text = ""
    Next cell
    '[4] return xml as string content
    s = xDoc.XML
End If

End Sub

Further hint to Example call

Instead of replacing the same range, you can also copy the whole data set (including formats) to another sheet via:

    Sheet2.Range("D2").Resize(rng.Rows.Count, rng.Columns.Count).value(11) = s

Caveat

As @ChrisNeilson pointed out, "this can also gives unexpected results in some circumstances where the range being processed by ClearFormulae includes some cells that contain formula referring to cells outside the range being processed".

Testing Value(11) against other solutions

Testing with a 20% formula rate (in a double column range) shows that the SpecialCells approach (posted by @Storax and @VBasic2008) starts extremely fast, but looses against my Value(11) approach as soon as the data range exceeds ~ 115,100 rows.

The HasFormula solution (@FaneDuru) seems to be restricted to smaller ranges getting soon time consuming at ranges over 10000 rows.