Conditional data cleanup

[PSD]

Hi all,

I have a question on how to implement a line of code, selecting a specific cell value (and deleting a different one).
What I mean by this is the following (see image):

I have a range of time data points in a single column (either empty or not, in time format).
Whenever there are two (or more) consecutive cells consisting data, I wish to ONLY keep the "highest" (longest) value, deleting the rest of the grouped values.

Does anyone have a suggestion on how to go about this?

 

[maabadi]

OK , if your first cell has data is A2 (if another cell change A2 at code to your cell Address), Test this code and you see result as message box.
then write here messagebox result.

Sub Test11()
MsgBox Range("A2").Value & "##"
End sub

if you see a.m. again at the end of result also try this code:

Sub Test()
Dim i As Long, j As Long, Lr As Long, Mx As Double
Lr = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:B" & Lr)
.Formula = "=iferror(left(A1,find("" "",A1) -1)-0.5,"""")"
.Value = .Value
End With
Range("B1:C" & Lr).NumberFormat = "h:mm:ss AM/PM"
For i = 2 To Lr
If j = 0 Then j = Range("B" & i).End(xlDown).Row
If Range("B" & j - 1).Value = "" Then
 j = j - 1
End If
If Range("B" & i) = Application.WorksheetFunction.Max(Range("B" & i & ":B" & j)) Then
Range("C" & i) = Range("B" & i)
 i = j
j = 0
End If
Next i
End Sub

 

[PSD]

Hi Maabadi,

The prompt shows as follows (so no AM appears here):

 

[maabadi]

Please upload example file on google drive and insert link here (description at below). we have problem with google sheets.

 

[PSD]

Hi Maabadi,

Excuse me for the delayed reaction. I have tried working out to upload the document, but every time I do, GoogleDrive just converts the sheet as if it were a Google Sheets file (where the data gets altered for some reason, to date-like data rather then entries in seconds) so thats very inconvenient as you are unable to look in to the file within Excel. I will try to see if I can get dummy numbers to work using your code anyway. I will let you know whether I could make sense of it eventually!

 

[maabadi]

Paste your Data at column F and after Running macro you see result at Column H.
TestPSD