macro to compare cells and delete

[srr797]

Hi

I am in need of a macro that will compare columns I and J. Both columns have times in them. I need the macro to delete (and shift up) the row if the time in column I is earlier than the time in column J.

For example cell I1 has 10:05am and cell J1 has 01:12pm. The macro would delete the row because it the time in column I is earlier than the time in column J.

Thanks in advance!

 

[Simon Lloyd]

try this:

Sub Del_Rows()
Dim Rng As Range, MyCell As Range
Set Rng = Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If MyCell < MyCell.Offset(0, 1) Then
MyCell.EntireRow.Delete Shift:=xlUp
End If
Next MyCell
End Sub

 

[Greg Truby]

When deleting rows, you can't loop top-to-bottom. The deletion results in the loop skipping over rows after deletions. You have to work bottom-to-top; something like so:

Sub KillTheEarlyBirds()
    Dim lngCounter  As Long, _
        lngLastRow  As Long
 
    Let lngLastRow = Range("I" & Rows.Count).End(xlUp).Row
 
    For lngCounter = lngLastRow To 2 Step -1
        If Range("I" & lngCounter).Value < Range("J" & lngCounter).Value Then
            Cells(lngCounter, 1).EntireRow.Delete
        End If
    Next lngCounter
End Sub

Note that this does what you ask - deletes the entire row and not just cells in columns I & J.

 

[srr797]

try this:

Sub Del_Rows()
Dim Rng As Range, MyCell As Range
Set Rng = Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If MyCell < MyCell.Offset(0, 1) Then
MyCell.EntireRow.Delete Shift:=xlUp
End If
Next MyCell
End Sub

Thanks for your help. Although it doesn't seem to do anything when I run that code.

 

[srr797]

When deleting rows, you can't loop top-to-bottom. The deletion results in the loop skipping over rows after deletions. You have to work bottom-to-top; something like so:

Sub KillTheEarlyBirds()
    Dim lngCounter  As Long, _
        lngLastRow  As Long
 
    Let lngLastRow = Range("I" & Rows.Count).End(xlUp).Row
 
    For lngCounter = lngLastRow To 2 Step -1
        If Range("I" & lngCounter).Value < Range("J" & lngCounter).Value Then
            Cells(lngCounter, 1).EntireRow.Delete
        End If
    Next lngCounter
End Sub

Note that this does what you ask - deletes the entire row and not just cells in columns I & J.

Thanks Greg... although that doesn't seem to do anything either. Could it be because the cells are in time format?

 

[Simon Lloyd]

Greg, sorry, can't believe i did that, still its late here so here's what i should have given.

Sub Del_Rows()
Dim i As Long
For i = Range("I" & Rows.Count).End(xlUp).Row To 2 Step -1
If Cells(i, "I") < Cells(i, "J") Then
Rows(i).EntireRow.Delete Shift:=xlUp
End If
Next i
End Sub

 

[Simon Lloyd]

Maybe your values aren't really times, maybe they are text?

 

[srr797]

Maybe your values aren't really times, maybe they are text?

Tried your other code... got the same result. Not sure why they would be text... i double checked the formatting and it is set to time.

 

[srr797]

also, just converted the format to numbers from time format and still not getting anything when i run the macro.

 

[srr797]

would it make a difference if the time in column J is from a vlookup?