Find and Replace alternative

[rstone25]

Hi,

I have a worksheet which has a large amount of data in. There are a number of cells which have a date of '01/01/70 00:00:00' in and I am wondering whether these can be changed to a blank cell without using Find and Replace. I would like to make it automatic as well if possible, or is it possible to make a macro.

Any help appriciated.

Ta

 

[Russell Hauf]

On 2002-02-26 08:48, rstone25 wrote:
Hi,

I have a worksheet which has a large amount of data in. There are a number of cells which have a date of '01/01/70 00:00:00' in and I am wondering whether these can be changed to a blank cell without using Find and Replace. I would like to make it automatic as well if possible, or is it possible to make a macro.

Any help appriciated.

Ta

Use Conditional Formatting. Select the range that contains these dates, then go to Format-Conditional Formatting. Under Conditon 1, change the "Between" box to "equal to", and in the next box type 25569.00. Then click on the "Format..." button, and change the Color combo box to have white text (or whatever color your background is). This way the value will be there, but you won't be able to see it.

Hope this helps,

Russell

 

[Guest]

If your dates are in a single column use an AutoFilter to select the rows containing this date. Select the cells containing the date. Choose Edit | Go To... | Special... Visible cells only. Finally, choose Edit | Clear All.

 

[NateO]

Not even an find and replace in vba? Like:

sub getridofit()
Cells.Replace What:="1/1/1970 12:00:00 AM", Replacement:="", LookAt:= _
xlPart, SearchOrder:=xlByColumns, MatchCase:=False
end sub

Regards, Nate
This message was edited by NateO on 2002-02-26 10:22

 

[NateO]

Or, if you want to leave your mark on the cells that were changed, you could do something like this (highlights cells blue):

Sub ReplaceIt()
Dim rFound As Range
Dim szFirst As String
Dim iCount As Integer
Dim oldval As Date
Dim newval As String
oldval = "1/1/1970 12:00:00 AM"
newval = ""
Set rFound = Cells.Find(What:=oldval, LookAt:=xlPart)
iCount = 0
Do While Not rFound Is Nothing
If szFirst = "" Then
szFirst = rFound.Address
ElseIf rFound.Address = szFirst Then
Exit Do
End If
rFound.Value = Application.Substitute(rFound.Value, _
oldval, newval)
rFound.Interior.ColorIndex = 32 'Leave your mark here
iCount = iCount + 1
Set rFound = Cells.FindNext(rFound)
Loop
End Sub

Or you can make the oldval refer to a cell, e.g.,

oldval = [a1] 'cell range


Cheers, Nate
This message was edited by NateO on 2002-02-26 10:33