macro to compare cells and delete

srr797

New Member
Joined
Nov 6, 2010
Messages
39
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
try this:
Code:
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
 
Upvote 0
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:

Code:
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.
 
Upvote 0
try this:
Code:
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.
 
Upvote 0
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:

Code:
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?
 
Upvote 0
Greg, sorry, can't believe i did that, still its late here so here's what i should have given.
Code:
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
 
Upvote 0
also, just converted the format to numbers from time format and still not getting anything when i run the macro.
 
Upvote 0

Forum statistics

Threads
1,214,749
Messages
6,121,309
Members
449,023
Latest member
MLPM

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