VBA Replace Dates

  • Thread starter Thread starter Matt Connor
  • Start date Start date
M

Matt Connor

Hi

I have an Excel worksheet which pulls data from SQL Server which treat null
dates as 01/01/1900

I'm trying to replace all such dates with blanks on the worksheet

In Excel when I go Edit >> Replace and enter Find What = 01/01/1900 leaving
Replace blank it works marvellously

However in VBA the following does not work (even though this was from
recording the above action)

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

I have tried also using numerics for the What:= again with no luck

Am I being stupid?

Thanks

Matt
 
Restrict your search range, then try searching for zero

Equally (but slower):

Dim cel as Range, rngNullDateRange as range
For Each cel in rngNullDateRange
if cel.value=0
cel.clearcontents
end if
next

of
For Each cel in rngNullDateRange
if cel.text="01/01/1900"
cel.clearcontents
end if
next
 
Hi Matt,
I have an Excel worksheet which pulls data from SQL Server which treat null
dates as 01/01/1900

I'm trying to replace all such dates with blanks on the worksheet
Click to expand...


if you have the chance try to solve the problem where the data is coming
from, eg. change the sql-statement to
IIF(mydate = 1/1/1900, NULL, mydate).

For me, changing the numberformat to standard and then replacing the value 1
with nothing worked, eg:
Range("B2:B9").Select
Selection.NumberFormat = "General"
Range("B7:B8").Select
Selection.Replace What:="1", Replacement:="test", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

arno
 
Back
Top