How to replace default date format (i.e. 0/1/1900) with blank in excel using Macro VBA-CodePudding

Sub Macro9()
  Range("Table57[Weld Done]").Select
  Selection.Replace What:="0/1/1900", Replacement:="", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=True, FormulaVersion:=xlReplaceFormula2
  Range("Table57[[#Headers],[Weld Done]]").Select
End Sub

I use Replace function to replace default date 0/1/1900 with "blank" & it worked. So I recorded the workflow using VBA macro recording function & it gives coding as above.

When I try to use the macro again using the same code, it won't return the "blank". The value remains as default date 0/1/1900 like nothing happened.

All of you can refer below image for reference.

Your assistance is highly appreciated. Thanks.

CodePudding user response：

The issue is that the number 0 formatted as date is 0/1/1900

I assume that they are the result of a calculation (you use a formula in those cells). You can change the .NumberFormat of your range to something like:

[=0]"";D/M/YYYY

and this will hide the zero dates and show the cells as blanks.

CodePudding user response：

Just change "0/1/1900" to 0 will do.

CodePudding user response：

What worked for me was the following:

Dim sht As Worksheet
Dim usedrng As Range

    Set sht = Sheets("Sheet1")
    Set usedrng = sht.UsedRange
    
    For Each cell In usedrng.Cells
        If cell.Value = "00:00:00" Then
            cell.Value = ""
        End If
    Next

The problem seems to be caused by the difference between the display and the value. My code looks at the underlying value.