Converting cells containing a null string constant to truly empty cells

Question

I am trying to write a subroutine that will convert cells containing a null string constant to truly empty cells.

The cells containing null string constants resulted from having formulas of the form IF(condition,1,"") and then copying those cells onto themselves using Paste Special..., Values.

Unfortunately, the Range.SpecialCells method does not appear to offer an option that will solve the problem.  The xlCellTypeBlanks constant does not work because the cells are not truly empty.

Can someone kindly suggest another alternative?  Thanks in advance for any assistance.

Answer 1

Try this macro. It will also clear cells that contain only soft or hard spaces.

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

You can, of course, replace Selection with a specific range.

Answer 2

Hans,

Thanks for your help.  Although your code ran fine, it, unfortunately, did not convert the cells containing null string constants to truly empty cells.

Also, I previously discovered that xlCellTypeConstants includes both non-null and null constants.  Too bad Microsoft didn't add an xlCellTypeNullConstants constant.

Answer 3

Gary's Student,

Thanks for your help!  Unfortunately, because there are some merged cells on my worksheet, I get an error message when r.Clear attempts to execute.

Also, please note that my worksheet contains a combination of cells with formulas, numeric constants, string constants, as well as null string constants.  The solution to my problem should only change those cells containing null string constants.

Answer 4

I am trying to write a subroutine that will convert cells containing a null string constant to truly empty cells.

I think this will work...

Sub ClearNonNullEmptyCells()

    Dim C As Range

    For Each C In Selection.Columns

        C.TextToColumns

    Next

End Sub

You didn't tell us where these cells are, so I just set the code up to handle the currently selected cells, but you can change the Selection reference to any valid range reference and the code should still work. By the way, you can do this manually if that will fit your work flow better.... select your cells, click "Data/Text To Columns" from the menu bar, and click the Finish button as soon as the dialog box appears.

Answer 5

Try this:

Sub CleanThem()

Set rr = Cells.SpecialCells(xlCellTypeFormulas)

For Each r In rr

    If r.Value = "" Then

        r.Clear

    End If

Next

End Sub