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

hnd12000

New Member
Joined
Feb 10, 2019
Messages
11
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,048
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

New Member
Joined
Feb 10, 2019
Messages
11
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,478
Messages
5,636,567
Members
416,924
Latest member
cmlacerna

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
Top