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!

 

[srr797]

So it seems since column J is a vlookup the macro won't compare the value.

 

[Simon Lloyd]

try it with static numbers to prove your theory then post back

 

[srr797]

try it with static numbers to prove your theory then post back

Ok so i just input some random static numbers and the macro works great! Now I just need a way to convert the value from the vlookup to a static number and paste it back into the original cell.

 

[Simon Lloyd]

I've just got back in, i was hoping that you'd find no difference!, if your found value for the vlookup is formatted as time and the cell that houses your vlookup is formatted as time the macro should work perfectly!

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

Click on the cells in question once then look at the formula bar, doest it show just the time or does it show time and date?, whatever it shows it must be consitent with column I, J and wherever your lookup result is from.

 

[AlphaFrog]

One problem might be if the formulas in column J are returning Day-Time values and not just Time-only values.

As a test, format both columns I and J as numbers. You should then see their time values as decimal numbers. If the values in column J include "Days", the values would be greater than 1. That may be the reason why column I is never less than column J.

You could modify the macro to strip any "Day" component from the time values if needed.

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