Find dates and delete text VBA

[mpatino]

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:

[TABLE="width: 314"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: left"]2018-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[/TD]
[/TR]
</tbody>[/TABLE]
Appreciate any assistance.

Thank you,

 

[Rick Rothstein]

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?

 

[mpatino]

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.

 

[Rick Rothstein]

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...

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

 

[mpatino]

That was absolutely awesome, thanks a lot for your quick assistance and for the code, this made the trick.

 

[mpatino]

Sorry to ask you again, but how can I do to run this code along the column until the last cell containing text?

 

[Rick Rothstein]

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...

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

 

[mpatino]

Again, thank you very much for your quick reply.