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!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
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
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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.
 

srr797

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

srr797

New Member
Joined
Nov 6, 2010
Messages
39

ADVERTISEMENT

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?
 

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
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
 

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756

ADVERTISEMENT

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

srr797

New Member
Joined
Nov 6, 2010
Messages
39
also, just converted the format to numbers from time format and still not getting anything when i run the macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

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
Top