Replace "" With Correct Blanks (Null)

Question

At my office while uploadin the data from an Excel on Temenos Database I need to have one of the fields to be either reflecting a proper date or be Null. While copying and pasting the values on the same sheet so as to remove the formulas, the formulas ending up to a result of ="" turn out to be the relevant cells neither be blank nor showin any date.

Any idea or shortcut or even a code to delete the cells' value/contents if the same had the said formula resulting into a "" instead of filtering all of the 105 columns one by one so as to result the blank cells (eventhough they consist of ="") and then delete the visible cells, at the earliest.?

Answer 1

I use this macro to "clean up" blank cells. It replaces clears all cells within the selected range that contain an empty string "" after replacing non-breaking spaces with ordinary spaces and trimming leading/trailing spaces. If there are formulas within the selection that result in an empty string, they will be removed.

Sub ClearBlanks()

    Dim oCell As Range

    On Error GoTo ExitHandler

    Application.ScreenUpdating = False

    For Each oCell In Selection

        If Trim(Replace(oCell.Value, Chr(160), " ")) = "" Then

            oCell.ClearContents

        End If

    Next oCell

ExitHandler:

    Application.ScreenUpdating = True

End Sub