delete the row that has the earliest time

G

gdgool

New Member
Joined
Aug 6, 2016
Messages
17
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:

HTML: 
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]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Here is how I did it:

Sheet 1 Setup:
Excel Workbook
ABCDE
1Numbertime stamprounddevice
2516/6/22:39:44515
3416/6/22:39:44216
4816/5/22:39:44817
5616/5/2:40:12218
6
Sheet1


After code runs:
Excel Workbook
ABCDE
1Numbertime stamprounddevice
2516/6/22:39:44515
3816/5/22:39:44817
4616/5/2:40:12218
5
Sheet1


Macro:
Code: 
Sub Delete_Earliest_Time()
Application.ScreenUpdating = False
Dim i As Long, lastrow As Long
Dim mylastrow As Long, myval As String, lastcolumn As Long
Dim add1 As Long, add2 As Long, xrow As Long

With ActiveSheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Range("C2").CurrentRegion.Sort key1:=Range("C2"), order1:=xlAscending, Header:=xlGuess

Range("B2:B" & lastrow).Select
    Selection.TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="/", FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 1)), _
    TrailingMinusNumbers:=True

Range("C:C").Copy Range("F1")
Range("F2").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.RemoveDuplicates Columns:=Array(1), Header:=xlNo
Selection.Sort key1:=ActiveCell, order1:=xlAscending

mylastrow = Cells(Rows.Count, "F").End(xlUp).Row

For i = mylastrow To 2 Step -1
    myval = Cells(i, "F").Value
    lastcolumn = _
    Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious).Offset(0, -2).Column
    add1 = Columns(3).Find(What:=myval, LookIn:=xlValues, LookAt:=xlWhole).Row
    xrow = add1
Do

If Cells(xrow + 1, 3).Value <> myval Then
    add2 = xrow + 1
    Exit Do
Else
    xrow = xrow + 1
End If

Loop Until xrow = lastrow + 1

add2 = xrow

Range(Cells(add1, 1), Cells(add2, lastcolumn)).Select

If Selection.Rows.Count < 2 Then
    Else
If Selection.Rows.Count > 1 Then
    If Cells(add1, lastcolumn + 1) > Cells(add2, lastcolumn + 1) Then
    Rows(add1).EntireRow.Delete Shift:=xlUp
End If
End If
End If

Next

Range("E:F").EntireColumn.Delete
Range("D2").CurrentRegion.Sort key1:=Range("D2"), order1:=xlAscending, Header:=xlGuess
Range("E1").Select

End With

Application.ScreenUpdating = True
End Sub
 
Upvote 0
michaelsmith559 said:
Here is how I did it:

Sheet 1 Setup:
Sheet1

ABCDE

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:90px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]time stamp[/TD]
[TD="align: center"]round[/TD]
[TD="align: center"]device[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]16/6/22:39:44[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]16/6/22:39:44[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]16/5/22:39:44[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]16/5/2:40:12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]18[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

After code runs:
Sheet1

ABCDE

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:90px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]time stamp[/TD]
[TD="align: center"]round[/TD]
[TD="align: center"]device[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]16/6/22:39:44[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]16/5/22:39:44[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]16/5/2:40:12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]18[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Macro:
Code: 
Sub Delete_Earliest_Time()
Application.ScreenUpdating = False
Dim i As Long, lastrow As Long
Dim mylastrow As Long, myval As String, lastcolumn As Long
Dim add1 As Long, add2 As Long, xrow As Long

With ActiveSheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Range("C2").CurrentRegion.Sort key1:=Range("C2"), order1:=xlAscending, Header:=xlGuess

Range("B2:B" & lastrow).Select
    Selection.TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="/", FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 1)), _
    TrailingMinusNumbers:=True

Range("C:C").Copy Range("F1")
Range("F2").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.RemoveDuplicates Columns:=Array(1), Header:=xlNo
Selection.Sort key1:=ActiveCell, order1:=xlAscending

mylastrow = Cells(Rows.Count, "F").End(xlUp).Row

For i = mylastrow To 2 Step -1
    myval = Cells(i, "F").Value
    lastcolumn = _
    Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious).Offset(0, -2).Column
    add1 = Columns(3).Find(What:=myval, LookIn:=xlValues, LookAt:=xlWhole).Row
    xrow = add1
Do

If Cells(xrow + 1, 3).Value <> myval Then
    add2 = xrow + 1
    Exit Do
Else
    xrow = xrow + 1
End If

Loop Until xrow = lastrow + 1

add2 = xrow

Range(Cells(add1, 1), Cells(add2, lastcolumn)).Select

If Selection.Rows.Count < 2 Then
    Else
If Selection.Rows.Count > 1 Then
    If Cells(add1, lastcolumn + 1) > Cells(add2, lastcolumn + 1) Then
    Rows(add1).EntireRow.Delete Shift:=xlUp
End If
End If
End If

Next

Range("E:F").EntireColumn.Delete
Range("D2").CurrentRegion.Sort key1:=Range("D2"), order1:=xlAscending, Header:=xlGuess
Range("E1").Select

End With

Application.ScreenUpdating = True
End Sub
Click to expand...


I'm actually dealing with a large amount of data with quite a lot of rows and columns the code you supplied is wokring very well for small amount of data but something as big as mine its only rearraging half of a row but just going to the bottom of the list not deleting. Is there another way of dealing with it. Im very new to VBA so im still not used to the terms yet but is there a way of using the if loops saying if the rows equal then. if the startime is less delete entire row of the least start time. i have another sub that is arranging the rows in ascending order with relation to column c so thats not needed and copy these columns to a new columns and dealing with the amount of data I have will slow the code down. i should apologise i made an rrror for row 4 it was mean to be
16/5/22:40:12 not 16/5/2:40:12. Any help would be really appreciated sorry i have so many questions i've tried doing myself but at a loss​
 
Upvote 0
I also need to mention the line that defines the last column. For the way I setup the sheet I wanted column 2 to be the last column. So I have to find the last column actually used and subtract 2 from it. So, for example if the last column that I used was column E/5, then I would have 5-2=3. The 3 is what I would have to use in the offset(0, -3). If you have alot of columns on your sheet this is one thing that would have to change. To help any further, whether it is me or someone else, we really need to see how your sheet is setup.
 
Upvote 0
What sort of date format is "16/6/22:39:44". if I copy and paste that into a cell, its format shows as a string, not date time, where is the year ????
Or is it me !!!!!
 
Upvote 0
MickG said:
What sort of date format is "16/6/22:39:44". if I copy and paste that into a cell, its format shows as a string, not date time, where is the year ????
Or is it me !!!!!
Click to expand...


its year/month/day:39:44 is the time it takes to complete a test its not date time so to speak more of when a test was done and how long it took with :39 being mintues and :44 being seconds
 
Upvote 0
MickG, I couldn't figure it out either, so in the code I did a text to columns to split the date part from the time and then compared the times.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
387
maiwarits
M
S
  • Solved
Delete rows before a certain time
2 3
Replies
26
Views
733
Joe4
Joe4
Y
  • Solved
Need formula to match
2
Replies
10
Views
596
yukonexcel
Y
J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
247
Peter_SSs
Peter_SSs
trishcollins
  • Solved
Excel vba set bold specific text of string into the cell in Pivot table.
Replies
2
Views
452
trishcollins
trishcollins

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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