The "empty" cells contain an empty string, which Excel doesn't see as the same as a real blank. In short, the 'blank' cells are not actually blank, thus rendering any VBA code that needs to work on blank cells unworkable.


To resolve, select the range you wish to clean and run the following VBA against it;


 

Sub ClearBlanks()
  Dim oCell As Range
  On Error GoTo ExitHandler
  Application.ScreenUpdating = False
  For Each oCell In Selection.SpecialCells(xlCellTypeConstants)
    If Trim(Replace(oCell.Value, Chr(160), " ")) = "" Then
      oCell.ClearContents
    End If
  Next oCell
ExitHandler:
  Application.ScreenUpdating = True
End Sub