Deleting formula when value is null

Question

I'm hoping there is a quick way to delete formulas in cells if the result in Null.

Specifically, using IFERROR, I have set up my formulas to read "" (i.e., blank) if the result of the formula is an error. While the cells look blank, they still contain the formula; therefore, I can't run statistical analyses (using XLSTAT) on the data because they are viewed as non-numerical cells. I tried Find and Replace but that didn't work. Given the high number of columns and volume of data, filtering each row for "blanks" and deleting those cells manually would take a long time. Any thoughts would be greatly appreciated.

Guessing you don't know VBA so do a find/replace "" to some random string then find replace said random string? — findwindow
Once you have the sheet looking the way you want, select the whole sheet, paste special into another sheet with only values. Then run the analysis on the second sheet. — Sablefoste
i'd suggest remove IFERROR and bring back the errors to the cells. Select the region where u want to delete the error cells. Hit Ctrl+G. a "Go To" window will appear. Click Special. Select Formula, uncheck all but the Errors checkbox. Click Ok. Then hit the delete on your keyboard. — Rosetta
KS Sheon- that is the perfect fix. Thank you all! I appreciate your chiming in and offering suggestions. — KRD

Gary's Student Gary's Student · Accepted Answer · 2016-01-05T00:05:31

Try running this short macro:

Sub SheetFixer()
   Dim r As Range, rng As Range
   On Error Resume Next
   Set rng = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)

   If Err.Number <> 0 Then
      Error.Number = 0
      On Error GoTo 0
      MsgBox "No formulas on sheet."
      Exit Sub
   End If

   For Each r In rng
      If r.Value = "" Then r.Clear
   Next r
   On Error GoTo 0
End Sub

Deleting formula when value is null

1 Answers