Deleting empty cells in a cell range is easy.

  1. Select the cell range
    deleting formulas evaluating to an empty text string7
  2. Press function key F5
    deleting formulas evaluating to an empty text string
  3. Click "Special..." button
    deleting formulas evaluating to an empty text string1
  4. Double click on "Blanks", see picture above.
    deleting formulas evaluating to an empty text string5
  5. Right click on a selected cell and click "Delete"

Formulas evaluating to an empty text string

What about formulas evaluating to an empty text string, like this formula:

=IF(A2<=50, "", "Above 50")

The above steps won´t work unless you change the formula to this:

=IF(A2<=50, 1/0, "Above 50")

The formula evaluates to an error:

deleting formulas evaluating to an empty text string2

It is possible to select cells evaluating to an error:

    1. Select the cell range.
    2. Press function key F5
    3. Click "Special..." button
      deleting formulas evaluating to an empty text string3
    4. Select Formulas and Errors, see picture above.

deleting formulas evaluating to an empty text string6

  1. Right click on a selected cell and click "Delete"

Macro

If you don´t want to use the above technique or perhaps can´t, the following macro deletes formulas evaluating to an empty text string:

Sub DeleteBlankRows()
For i = Selection.Cells.Count To 1 Step -1
If Len(Selection.Cells(i)) = 0 Then Selection.Cells(i).Delete xlUp
Next i
End Sub

How to use macro

  1. Select a cell range
  2. Press Alt + F8 to open the macro dialog box
  3. Select macro "DeleteBlankRows"
  4. Click Run

This macro deletes the entire row if cell formula evaluates to an empty text string:

Sub DeleteBlankRows()
For i = Selection.Cells.Count To 1 Step -1
If Len(Selection.Cells(i)) = 0 Then Selection.Cells(i).EntireRow.Delete xlUp
Next i
End Sub

It is not possible to undo a macro so make sure you have made a backup!

Download excel *.xlsm file

Delete formulas evaluating to an empty string.xlsm