My spreadsheet has a couple of date columns. Some of the rows would have no
date in it. However, after running the macros - which uses these cell value
as "blank", the date of "1/1/1900" appearred in those blank cells and I
could not make it disappear.

Do you have any suggestion to make that date disappear? Since I am using
VBA, I would prefer some command scripts that would turn those cells "off".

Thanks in advance.

DP

DP

Your code must be returning a 1 somehow which is converted to that date (Day
1 in xl = 1/1/1900, you must explicitly test for the blank cell and then
return a blank cell on that test

If Range("A1").Value="" Then
'My destination range =""
Range.Offset (0,1).Value=""
End If

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog


"HaoHoaMastercard" wrote in message
t...
My spreadsheet has a couple of date columns. Some of the rows would have
no date in it. However, after running the macros - which uses these cell
value as "blank", the date of "1/1/1900" appearred in those blank cells
and I could not make it disappear.

Do you have any suggestion to make that date disappear? Since I am using
VBA, I would prefer some command scripts that would turn those cells
"off".

Thanks in advance.

DP

Change the format of the cell with
Range("your_cells").numberformat="dd/mm/yyyy;;"

The double semi-colons at the end mean that negative numbers and zeros
(which is what 1/1/1900 is) are not shown.


"HaoHoaMastercard" wrote:

My spreadsheet has a couple of date columns. Some of the rows would have no
date in it. However, after running the macros - which uses these cell value
as "blank", the date of "1/1/1900" appearred in those blank cells and I
could not make it disappear.

Do you have any suggestion to make that date disappear? Since I am using
VBA, I would prefer some command scripts that would turn those cells "off".

Thanks in advance.

DP

Hi Sam,

I thought 1/1/1900 is serial number 1. Serial number 0 would produce
1/0/1900 which really is a (qoute DP) date anomaly. Negitive numbers
give something like #########. At least, when using the regular date
system on an 'IBM compatible PC'.
After changing to the Apple (?) 1904 date system, 0 is 01-01-1904 etc.
Negative numbers give dates 'before' that date. Funny things happen.

Frans

"Sam Wilson" schreef in bericht
...
Change the format of the cell with
Range("your_cells").numberformat="dd/mm/yyyy;;"

The double semi-colons at the end mean that negative numbers and zeros
(which is what 1/1/1900 is) are not shown.


"HaoHoaMastercard" wrote:

My spreadsheet has a couple of date columns. Some of the rows would have
no
date in it. However, after running the macros - which uses these cell
value
as "blank", the date of "1/1/1900" appearred in those blank cells and I
could not make it disappear.

Do you have any suggestion to make that date disappear? Since I am using
VBA, I would prefer some command scripts that would turn those cells
"off".

Thanks in advance.

DP

Hi Nick,

I agree on the expected 1, formatted as date in the 'anomaly cells'. But
your code
puzzles me. Does it work on your computer?

Frans

"Nick Hodge" schreef in bericht
...
DP

Your code must be returning a 1 somehow which is converted to that date
(Day 1 in xl = 1/1/1900, you must explicitly test for the blank cell and
then return a blank cell on that test

If Range("A1").Value="" Then
'My destination range =""
Range.Offset (0,1).Value=""
End If

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog


"HaoHoaMastercard" wrote in message
t...
My spreadsheet has a couple of date columns. Some of the rows would have
no date in it. However, after running the macros - which uses these cell
value as "blank", the date of "1/1/1900" appearred in those blank cells
and I could not make it disappear.

Do you have any suggestion to make that date disappear? Since I am using
VBA, I would prefer some command scripts that would turn those cells
"off".

Thanks in advance.

DP