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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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,022
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,855
Messages
5,833,998
Members
430,250
Latest member
Reggie Mcqueen

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