[excel] Excel telling me my blank cells aren't blank

My method is similar to Curt's suggestion above about saving it as a tab-delimited file and re-importing. It assumes that your data has only values without formulas. This is probably a good assumption because the problem of "bad" blanks is caused by the confusion between blanks and nulls -- usually in the data imported from some other place -- so there shouldn't be any formulas. My method is to parse in place -- very similar to saving as a text file and re-importing, but you can do this without closing and re-opening the file. It's under Data > Text-to-Columns > delimited > remove all parsing characters (can also choose Text if you want) > Finish. This should cause Excel to re-recognize your data from scratch or from text and recognize blanks as really blank. You can automate this in a subroutine:

Sub F2Enter_new()         
   Dim rInput As Range
   If Selection.Cells.Count > 1 Then Set rInput = Selection
   Set rInput = Application.InputBox(Title:="Select", prompt:="input range", _ 
                                     Default:=rInput.Address, Type:=8)
'   Application.EnableEvents = False: Application.ScreenUpdating = False
   For Each c In rInput.Columns
      c.TextToColumns Destination:=Range(c.Cells(1).Address), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
      FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
   Next c
   Application.EnableEvents = True: Application.ScreenUpdating = True
End Sub

You can also turn-on that one commented line to make this subroutine run "in the background". For this subroutine, it improves performance only slightly (for others, it can really help a lot). The name is F2Enter because the original manual method for fixing this "blanks" problem is to make Excel recognize the formula by pushing F2 and Enter.