delete the row that has the earliest time

[gdgool]

hi I'm new to vba and I'm trying to write a code that will delete duplicate rows if say values in column C match up. But I want to delete the row that has the earliest time stamp heres an example.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]time stamp[/TD]
[TD]round[/TD]
[TD]device[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]16/6/22:39:44[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]16/6/22:39:44[/TD]
[TD]2[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]16/5/22:39:44[/TD]
[TD]8[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]16/5/2:40:12[/TD]
[TD]2[/TD]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]


Since in column C row 2 and 4 have the same value i want VBA to delete row 2 since the time stamp is earlier than row 4. heres what the output would look like

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]time stamp[/TD]
[TD]round[/TD]
[TD]device[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]16/6/22:39:44[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]16/6/22:39:44[/TD]
[TD]8[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]16/5/2:40:12[/TD]
[TD]2[/TD]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]


heres what i have so far:

Sub Dup()  

  Dim rCell  As Range    
Dim Lastrow As Long    
Dim rngDel  As Range        
   
 Lastrow = Range("C" & Rows.Count).End(xlUp).Row   
 For Each rCell In Range("C1:C" & Lastrow)       
 If Application.CountIf(Range(rCell, Range("C" & Lastrow)), rCell) > 1 Then          
  If rngDel Is Nothing Then                
Set rngDel = rCell.EntireRow           
 Else               
 Set rngDel = Union(rngDel, rCell.EntireRow)            
End If        
End If    
Next rCell   
 If Not rngDel Is Nothing Then rngDel.DeleteEnd Sub

This code is deleting the first duplicate but i want it to delete the row that has the earliest time stamp. Thanks for the help[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[michaelsmith559]

Don't know. We'll have to wait until the sheet gets posted. The macro I did assumed the sheet was setup similar to his post, but if it has alot of columns and reading about how he did the time stamp, the macro will have to change significantly.

 

[gdgool]

Hi, thanks for help I'm not quite sure how to post up the excel sheet but basically I'm dealing with data that is going as far as columns "HF" I'm importing large sets of data which takes up over 1000 rows , using excel 2010. What I have done to make it easier is I've assembled all the rows in ascending order by looking at column "GD" for simplicity purposes I used column "C" so what I'm trying to do is if the values in column "GD" match up delete the entire row that has the earliest time stamp. Since I'm importing soo much data I'm trying to clean it up the reason I need to take time stamp into account is the sometimes the data is being replicated on a later day. Also thank you for pointing out about sectioning off the time what I'm concerned with is the date which is in column "GX" in my case I used column "B" in the example. The time beside :39 minutes :42 seconds doesn't concern me but it's done that way in the files I'm importing it's best to section it off. I hope this makes sense.

 

[michaelsmith559]

If your last column is HF, can you put this formula in HG (I am assuming the first row of data is row 2) and copy down so that the time is separated from the date?:

=RIGHT(GX2,LEN(GX2)-FIND(":",GX2)

This should return for example 39:42 instead of :39:42.

If this is possible and you have the data ascending according to column "GD", then part of the code I gave you will create a unique list of the values in "GD" and will select all rows containing "GD". I did this so that I could group all the time stamps together. Once that is done, you could then compare the times and delete the earliest row.