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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
if you treat it as the number it is it should be zero so LookAt:=xlWhole, should do that
 

Joe4

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
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
what if you cut it down to what you need

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
@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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Excel keeps surprising
 

Joe4

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
I just hate doing anything with dates in VBA, as they're a right PITA. :mad:
 

Watch MrExcel Video

Forum statistics

Threads
1,129,491
Messages
5,636,633
Members
416,932
Latest member
mm07

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