Find and Replace Dates in VBA

[Guest]

If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it
works fine. However, when I insert the following code into my VBA project,
nothing happens. I took it straight from the good old Macro recorder!

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

Removing the quotes automatically changed it to this, with the same result:
Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

What have I done wrong, or what do I need to? I know this is basically zero
in date format but I don't want to replace everything that is 0, just those
looking like a date

Many thanks for your help.

 

[Guest]

Give this a try:

Sub ladym()
Set rr = Nothing
For Each r In Selection
If r.Text = "00/01/1900" Then
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Next
If rr Is Nothing Then
Else
rr.Clear
End If
End Sub

 

[Don Guillett]

From a post by Bob Phillips modified by Bernard V Liengme

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

 

[Guest]

Can't get this solution to work, keep getting type mismatch errors.

 

[Guest]

Unfortunately, nothing happened when I ran this

Give this a try:

Sub ladym()
Set rr = Nothing
For Each r In Selection
If r.Text = "00/01/1900" Then
If rr Is Nothing Then
Set rr = r
Else
Set rr = Union(rr, r)
End If
End If
Next
If rr Is Nothing Then
Else
rr.Clear
End If
End Sub

 

[Guest]

Sorry if this is a duplicate post, internet connection having a funny five
minutes.

Nothing seemed to happen when I ran this solution.

 

[Guest]

This requires you to select a group of cells prior to running the macro.

 

[Don Guillett]

I tested.
What are you trying to do and exactly what did you do with the offering?

 

[Guest]

The problem is that you are not stating dates clearly enough for Excel. I
recommend using the date function instead of entering dates like 01/12/2007.
You would have something like:

A B C
D
------------------------------------------------------------------------
1 'Date Year Month
Day
2 '=Date(B2, C2, D2) 2007 1 12

This way VB never makes mistakes with dates. I must admit this solution
requires you to change your workbook is written and that you might lack the
time, but consider using it on new workbooks.

 

[Guest]

I copied and pasted it, replaced "A" with "00/01/1900" and kept getting type
mismatch errors on running it.
Should I have done something else?

 

[Guest]

Didn't realise, will try again although I have to say I don't understand
quite what it's doing. Will this replace all instances of 00/01/1900 with an
empty cell? There are no empty cells - they are all filled with dates.

 

[Guest]

I'm just dealing with imported data. It is exported as csv with blank cells
where there are no dates against a particular item. I do a vlookup to pull
the information I want and that gives me the result of 00/01/1900 where there
is no date in the source field. The vlookup is one of many that is part of a
nested IF that is currently four deep so I could investigate adding something
else in to try and remove the zero dates. I was just hoping for a quick
solution that would replicate find/replace as I will have to amend formulae
across 24 columns that all look at varying criteria.

 

[Dave Peterson]

Any chance you your dates are in specific columns?

If yes, then maybe you can change the number format of the columns to General,
then change 0 to "". Then change the number format back to the date format you
like.

With ActiveSheet.Range("a:a,c:c,f:h")
.NumberFormat = "General"
.Cells.Replace what:="0", replacement:="", lookat:=xlWhole
.NumberFormat = "mm/dd/yyyy"
End With

 

[Guest]

I tried it again using ActiveSheet.UsedRange.Select and Columns("J:AG").Select.

Nothing changed.

 

[Guest]

Worked perfectly, thank you!

Any chance you your dates are in specific columns?

If yes, then maybe you can change the number format of the columns to General,
then change 0 to "". Then change the number format back to the date format you
like.

With ActiveSheet.Range("a:a,c:c,f:h")
.NumberFormat = "General"
.Cells.Replace what:="0", replacement:="", lookat:=xlWhole
.NumberFormat = "mm/dd/yyyy"
End With

 

[Guest]

If you select a block of cells and run the macro, any cell containing
00/01/1900 will be cleared.