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

 

[GWteB]

??

 

[Joe4]

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

I imagine it is even worse for you guys (than it is for us), as VBA uses the American format of dates by default. I have seen many threads with the issues it causes over there.

 

[Fluff]

Yup, you do almost anything to a date & it suddenly changes to mm/dd, so either the code doesn't work, or you get odd results.
I often convert them to numbers first & then work on them.

 

[Joe4]

I often convert them to numbers first & then work on them.

Smart work-around.

 

[MARK858]

I often convert them to numbers first & then work on them.

The last firm that I worked for (where we regularly sent reports between 4 different countries), I eventually convinced everyone to convert any date columns to numbers before sending out the reports (mainly because I normally got sent the reports when they went wrong here).

 

[hnd12000]

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

Thanks Fluff !

In fact trying your proposal I realised that the

  .NumberFormat = "dd/mm/yyyy"

was not necessary and after removing it I can select the whole page and it removes all whole "0" no matter if they are 0/1/1900 or simple digit, while keeping it was changing all non affected data to date format.

 

[Fluff]

Glad we could help & thanks for the feedback.