Unable to replace zero date (0/1/1900)

[hnd12000]

I have cell with at date that should not exist that is 0/1/1900.
I'm trying to find a way in VBA to replace all such values by nothing.

I tried :

Selection.Replace What:=0, Replacement:=""
Selection.Replace What:="0/1/1900", Replacement:=""
Selection.Replace What:=Format("0/1/1900", "D/M/YYYY"), Replacement:=""
Selection.Replace What:=Format(DateValue("0/1/1900"), "D/M/YYYY"), Replacement:=""
Selection.Replace What:=Format(DateValue(0), "D/M/YYYY"), Replacement:=""

The funny thing is that when you records it works and gives you

    Cells.Replace What:="0/1/1900", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

but when you replay the values are just never removed

 

[mole999]

if you treat it as the number it is it should be zero so LookAt:=xlWhole, should do that

 

[Joe4]

if you treat it as the number it is it should be zero so LookAt:=xlWhole, should do that

I thought the same thing, and tried it out, but it didn't for me either. I tried various different options, and couldn't get any to work.
Were you able to get it to work?
I wonder if it has something to do with VBA using an American version of Excel versus a European version. I know things something get a little "funny" when it comes to dates in VBA between the different versions.

 

[mole999]

I have cell with at date that should not exist that is 0/1/1900.
I'm trying to find a way in VBA to replace all such values by nothing.

I tried :

Selection.Replace What:=0, Replacement:=""
Selection.Replace What:="0/1/1900", Replacement:=""
Selection.Replace What:=Format("0/1/1900", "D/M/YYYY"), Replacement:=""
Selection.Replace What:=Format(DateValue("0/1/1900"), "D/M/YYYY"), Replacement:=""
Selection.Replace What:=Format(DateValue(0), "D/M/YYYY"), Replacement:=""

The funny thing is that when you records it works and gives you

    Cells.Replace What:="0/1/1900", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

but when you replay the values are just never removed

what if you cut it down to what you need

Cells.Replace What:="0/1/1900", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows

 

[Fluff]

Easiest option, if the dates do not have a time element, is probably like this

With Selection
   .NumberFormat = "0"
   .Replace 0, "", xlWhole, , False, , False, False
   .NumberFormat = "dd/mm/yyyy"
End With

 

[GWteB]

@Fluff , I agree. The Replace method acts on strings and not on dates. In fact a date in Excel is a numeric value (with a date "tag"), although its appearance looks like a string, determined by the cell formatting. Change the cell formatting to number, general or text and then confirm that replacing a 0 with an empty string ("") will work.

 

[Joe4]

Interesting. On my American version, this did NOT work:

Cells.Replace What:="0/1/1900", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows

but this did:

With Selection .NumberFormat = "0" .Replace 0, "", xlWhole, , False, , False, False .NumberFormat = "dd/mm/yyyy" End With

 

[mole999]

Excel keeps surprising

 

[Joe4]

@Fluff , I agree. The Replace method acts on strings and not on dates. In fact a date in Excel is a numeric value (with a date "tag"), although its appearance looks like a string, determined by the cell formatting. Change the cell formatting to number, general or text and then confirm that replacing a 0 with an empty string ("") will work.

I know that the date is just a format, which is why I expected the "Replace 0" option to work, though it did not. Seems it wasn't quite that simple!

 

[Fluff]

I just hate doing anything with dates in VBA, as they're a right PITA.