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

hnd12000

New Member
Joined
Feb 10, 2019
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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 :
VBA Code:
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
VBA Code:
    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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
Easiest option, if the dates do not have a time element, is probably like this
VBA Code:
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
VBA Code:
  .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.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top