Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Find and Replace alternative

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Guest

    Default

    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.

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •