Find dates and delete text VBA

mpatino

Board Regular
Joined
Jul 8, 2009
Messages
82
Hi gurus,

I am looking for a VBA solution to check a cell which contains several updates and only keep the latest update, each update has its updated date and time. See the example below:

M
22018-05-18 01:02:09 - K (Work notes)
As per suggestion, implemented the fix. No reoccurrence of the issue so far.

2017-11-08 21:25:51 - E (Work notes)
Stopped replication batch jobs
Stopped the replication models using - /SAPAPO/REPL_STOP
Reactivate the replication models using the program - /SAPAPO/OM_REPL_MODEL_EXT
Checked with business user for any issues while performing Single Click PRF – No issues
Repeated the failed job to success.
Restarted the replication batch jobs. So far no ABAP dumps and no new issues were noticed.

2017-11-06 03:08:48 -Y (Work notes)
Hi Team,,

Created high priority SAP message with SAP -

551624 / 2017 COMMIT/ROLLBACK error on DEFAULT Database Connection

<tbody>
</tbody>
Appreciate any assistance.

Thank you,
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Are you dates always in descending date order as shown in your example? If so, then you want to delete everything after the first date's information, correct?
 
Upvote 0
Hi Rick,

Thanks for taking the time to review my question. Yes, the dates will always be in descending order and yes, I want to keep only the latest update and remove everything else after the second date is found.
 
Upvote 0
Thanks for taking the time to review my question. Yes, the dates will always be in descending order and yes, I want to keep only the latest update and remove everything else after the second date is found.
I think this macro may do what you want...
Code:
Sub LeaveInfoForLatestDateInCellM2()
  Dim N As Long, Result As String, Info() As String
  Info = Split(Range("M2").Value, vbLf)
  Result = Info(0)
  For N = 1 To UBound(Info)
    If Left(Info(N), 10) Like "####-##-##" Then
      Exit For
    Else
      Result = Result & vbLf & Info(N)
    End If
  Next
  Do While Right(Result, 1) = vbLf
    Result = Left(Result, Len(Result) - 1)
  Loop
  Range("N2") = Result
End Sub
 
Upvote 0
That was absolutely awesome, thanks a lot for your quick assistance and for the code, this made the trick.
 
Upvote 0
Sorry to ask you again, but how can I do to run this code along the column until the last cell containing text?
 
Upvote 0
Sorry to ask you again, but how can I do to run this code along the column until the last cell containing text?
This should do it for you...
Code:
Sub LeaveInfoForLatestDateInCellM2()
  Dim R As Long, N As Long, Result As String, Info() As String
  For R = 2 To Cells(Rows.Count, "M").End(xlUp).Row
    Info = Split(Cells(R, "M").Value, vbLf)
    Result = Info(0)
    For N = 1 To UBound(Info)
      If Left(Info(N), 10) Like "####-##-##" Then
        Exit For
      Else
        Result = Result & vbLf & Info(N)
      End If
    Next
    Do While Right(Result, 1) = vbLf
      Result = Left(Result, Len(Result) - 1)
    Loop
    Cells(R, "N").Value = Result
  Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top