Evaluate column and replace existing values

[SamPaulRoger]

Hi,

I am trying to have a macro evaluate column D which has dates listed, however, several in which are 1/0/1900 which I want to replace with nothing. Does anyone have any suggestions?

 

[SamPaulRoger]

**Bump**

I have worked out this much, however, when I run the script, it lists false through the whole column, I would like it to replace anything that equal's the "12:00:00 AM" value listed, however, leave the other dates listed. Any thoughts?

Sub Clear_Dates()
    Dim lRow As Long
    lRow = Sheet13.Cells(Rows.Count, "D").End(xlUp).Row
    Sheet13.Range("D2:D" & lRow).Value = Sheet13.Evaluate("IF(D2:D" & lRow & "=""12:00:00 AM"","""")")
End Sub

Sheet13 before

Dates

7/30/2018

1/0/1900

8/2/2018

<tbody>
</tbody>

Sheet13 after (The Goal)

Dates

7/30/18

8/2/2018

<tbody>
</tbody>

Sheet13 after (with current code above)

Dates

False

False

False

<tbody>
</tbody>

 

[nikio8]

Hi sorry to say so, this quite basic question, you could use google. Date is just a number. Time is decimal.

Sub Clear_Dates()    
    Dim lRow, i As Long
    lRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
     
    For i = 2 To lRow
        If (Cells(i, "A").Value) < 1 Then ' -- you can use this too, it is the same ---   < DateValue("1 Jan 1900") Then
            Cells(i, "A").Value = Null
        End If
        
    Next i
End Sub

 

[SamPaulRoger]

nikio8,

This is really slow for 120K rows, is there anyway to speed this process up?

 

[Joe4]

How about simply highlighting column D, and doing a Find/Replace, replacing "1/0/1900" with nothing, and then selecting "Replace All"?

 

[Rick Rothstein]

Does this work for you...

[table="width: 500"]
[tr]
	[td]Sub ClearDateZeroCells()
  Columns("A").Replace "1/0/1900", "", xlWhole, , , , False, False
End Sub[/td]
[/tr]
[/table]

Note: You can do this manually using Excel's Replace dialog box.

 

[SamPaulRoger]

Does this work for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ClearDateZeroCells()
  Columns("A").Replace "1/0/1900", "", xlWhole, , , , False, False
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Note: You can do this manually using Excel's Replace dialog box.

Thank you Rick

Exactly what I was looking for, This processes 120,000 rows in a split second and I do not have to worry about formulas being deleted or providing extra steps to anyone else who have far less experience than what I do. I Appreciate the help

 

[Joe4]

Note: You can do this manually using Excel's Replace dialog box.

Yep, that is what I suggested right before Rick's reply.

Note that Rick's code is just using VBA to do the same thing as doing it manually.