Find and Replace Dates in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
Unfortunately, nothing happened when I ran this

Gary''s Student said:
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
Click to expand...
 
Sorry if this is a duplicate post, internet connection having a funny five
minutes.

Nothing seemed to happen when I ran this solution.
 
I tested.
What are you trying to do and exactly what did you do with the offering?
 
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.
 
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?
 
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.
 
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.
 
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
 
I tried it again using ActiveSheet.UsedRange.Select and Columns("J:AG").Select.

Nothing changed.
 
Worked perfectly, thank you!

Dave Peterson said:
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
Click to expand...
 
If you select a block of cells and run the macro, any cell containing
00/01/1900 will be cleared.
 
Back
Top