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?

 

[PSD]

Hi Fluff,

I believe it was/is. When running it I have the sheet open on which I want the macro to act at least.

 

[Fluff]

That's fine, if it's still not working you will need to wait for maabadi to log in again.

 

[maabadi]

How you run the macro at Excel window?
1. Press ALT, then W, M, V then Select Macro Name here Test and Then Run
2. Press ALT, Then L, PM then Select Macro Name here Test and Then Run
or ...

 

[PSD]

Hi Maabadi,

I tried both your short-cut suggestions and they both direct me to the prompt I indeed used to try and run the macro. However, even though it actively runs without producing an error, (I see it loading, as it did before) it does not impact the Excel sheet. I tried changing the "Macros in" option to either one of the three options, without success. I also tried to change the data type from time to general, to no avail.
Myself I could not think of anything else which might be the reason why I can not get it to work unfortunately.
Is there anything else I could provide you with so that you might have an idea why it does not work?

 

[maabadi]

Try this:

Sub Test()
Dim i As Long, j As Long, Lr As Long, Mx As Double
Lr = Range("F" & Rows.Count).End(xlUp).Row
For i = 1 To Lr
If j = 0 Then j = Range("F" & i).End(xlDown).Row
If Range("F" & j - 1).Value = "" Then
 j = j - 1
End If
If Range("F" & i) = Application.WorksheetFunction.Max(Range("F" & i & ":F" & j)) Then
Range("H" & i).Value = Range("F" & i).Value
 i = j
j = 0
End If
Next i
End Sub

 

[PSD]

Hi Maabadi,

Unfortunately I still fail to get results. Is there something else in the Microsoft Visual Basic for Apps overview which I could consider changing or fixing? If the code works for you, there should be a fault at my end.
Thank you for all the help so far by the way, irrespective of result.

 

[maabadi]

If it's possible upload copy of your file with test data on googledrive or other free hosting site ( I decribe at below of my post) and insert link here to I can check your file.

 

[PSD]

Hi Maabadi,

Below I posted a link to a sample data on google drive. Hopefully it helps with finding the issue.

Macro Data Example.xlsm

 

[maabadi]

Did you input a.m. manually in cells or insert data from other applications?

 

[PSD]

Hi Maabadi,

Strangely enough I did not add that manually at all, and it also did not show in the original file, where the posted data is a small sample of.
When extracting this and saving it in the drive I now see the "a.m." appearing as you see it in google sheets.
Can this be the underlying reason? I would imagine that this could impact the execution of your macro code.

Edit: The data was thus indeed inserted from an other application