I'm trying to delete formula formulas in empty cells cause my spreadsheet is too heavy and just save it take like 30m. The last personn using this file put formulas in every cells.

Sub clearFormulaEmptyCells()
 Dim sh As Worksheet, rngForm As Range, rngDel As Range, cF As Range
 
 Set sh = ActiveSheet
 On Error Resume Next
  Set rngForm = sh.UsedRange.SpecialCells(xlCellTypeFormulas)
 On Error GoTo 0
 If rngForm Is Nothing Then Exit Sub
 For Each cF In rngForm.Cells      'iterate between cells having formula
    If cF.Value = "" Then          'find the ones returning the null strinig
        If rngDel Is Nothing Then  'if rngDel not Set:
            Set rngDel = cF        'Set it as first such a cell
        Else
            Set rngDel = Union(rngDel, cF) 'make a Union between the existing range and the cell returning a null string
        End If
    End If
 Next
 If Not rngDel Is Nothing Then rngDel.ClearContents  'it will clear the formula
End Sub

I try this but when i run it i have a mismatch type error 13 and excel underlign this row : If cF.Value = "" Then

Below the kind of formulas that i have in cells :

=IFERROR(IF(AND(H398<>"";M398<>"");H398-M398;"");"")
=IF(G400<>"";G400;"")

Every formulas have a IF or IFERROR.

Thanks for your help!

I'm trying to delete formula formulas in empty cells cause my spreadsheet is too heavy and just save it take like 30m. The last personn using this file put formulas in every cells.

Sub clearFormulaEmptyCells()
 Dim sh As Worksheet, rngForm As Range, rngDel As Range, cF As Range
 
 Set sh = ActiveSheet
 On Error Resume Next
  Set rngForm = sh.UsedRange.SpecialCells(xlCellTypeFormulas)
 On Error GoTo 0
 If rngForm Is Nothing Then Exit Sub
 For Each cF In rngForm.Cells      'iterate between cells having formula
    If cF.Value = "" Then          'find the ones returning the null strinig
        If rngDel Is Nothing Then  'if rngDel not Set:
            Set rngDel = cF        'Set it as first such a cell
        Else
            Set rngDel = Union(rngDel, cF) 'make a Union between the existing range and the cell returning a null string
        End If
    End If
 Next
 If Not rngDel Is Nothing Then rngDel.ClearContents  'it will clear the formula
End Sub

I try this but when i run it i have a mismatch type error 13 and excel underlign this row : If cF.Value = "" Then

Below the kind of formulas that i have in cells :

=IFERROR(IF(AND(H398<>"";M398<>"");H398-M398;"");"")
=IF(G400<>"";G400;"")

Every formulas have a IF or IFERROR.

Thanks for your help!

• excel
• vba

Share Improve this question Follow edited Mar 17 at 8:40 FunThomas 30.1k44 gold badges2323 silver badges3838 bronze badges asked Mar 17 at 8:36 user29982961user29982961 511 bronze badge 2

• Do you mean there's a difference between =IF(G400<>"";G400;"") and =G400?

  发布者：admin，转转请注明出处：http://www.yc00.com/questions/1744572565a4581542.html