How to replace default date (e.g. 0/1/1900) with blank using VBA?

user_18858037 0
Viewed 310
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 used the 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, the value remains as default date 0/1/1900.

Replace default date 0/1/1900 with "blank"

vba excel
2 Answers

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

user_177949360

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.

user_32196130
Related