Replace date 00.01.1900
 

I want vb to replace all dates 00.01.1900 (=0 in general) in a spreadsheet
with blank (""), but Find a d Replace don't find any occurences, even if
there are so.
--
~roar

Replace date 00.01.1900
 

Hi

You can do this via number formatting:

If you right-click on the cells and select Format Cells, on the number
tab choose custom and use something like:

dd.mm.yyyy;;

Note the semi-colons on the end. Then any 00.01.1900 will be
represented on the sheet as blanks.

Hope this helps!

Richard


Roar wrote:

I want vb to replace all dates 00.01.1900 (=0 in general) in a spreadsheet
with blank (""), but Find a d Replace don't find any occurences, even if
there are so.
--
~roar

Replace date 00.01.1900
 

Thanks, I tryed, but the purpose is to clear all cells with the date
00.01.1900. If these cells are not cleared/blank, the automatic print area
setting is including these blank rows when printing.

Appreciate you assistance.
--
~roar


RichardSchollar skrev:

Hi

You can do this via number formatting:

If you right-click on the cells and select Format Cells, on the number
tab choose custom and use something like:

dd.mm.yyyy;;

Note the semi-colons on the end. Then any 00.01.1900 will be
represented on the sheet as blanks.

Hope this helps!

Richard


Roar wrote:

I want vb to replace all dates 00.01.1900 (=0 in general) in a spreadsheet
with blank (""), but Find a d Replace don't find any occurences, even if
there are so.
--
~roar

Replace date 00.01.1900
 

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If IsDate(Replace(.Cells(i, TEST_COLUMN).Value, ".", "/")) Then
.Cells(i, TEST_COLUMN).ClearContents
End If
Next i

End With

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Roar" wrote in message
...
Thanks, I tryed, but the purpose is to clear all cells with the date
00.01.1900. If these cells are not cleared/blank, the automatic print area
setting is including these blank rows when printing.

Appreciate you assistance.
--
~roar


RichardSchollar skrev:

Hi

You can do this via number formatting:

If you right-click on the cells and select Format Cells, on the number
tab choose custom and use something like:

dd.mm.yyyy;;

Note the semi-colons on the end. Then any 00.01.1900 will be
represented on the sheet as blanks.

Hope this helps!

Richard


Roar wrote:

I want vb to replace all dates 00.01.1900 (=0 in general) in a
spreadsheet
with blank (""), but Find a d Replace don't find any occurences, even
if
there are so.
--
~roar

Replace date 00.01.1900
 

I think Bob misread your message. I understood it to be that you want the
zero date (not all dates) deleted. Here is a minor modification
Public Sub ProcessData2()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If IsDate(.Cells(i, TEST_COLUMN).Value) And .Cells(i,
TEST_COLUMN).Value = 0 Then
.Cells(i, TEST_COLUMN).ClearContents
End If
Next i

End With

End Sub

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email