Macro to Delete Certain Rows
 

I have a spreadsheet that I need a macro for that will go through the
spreadsheet and delete lines that are nearly duplicate. For example, column
A will be a date, and column B an item. If there are two rows that have
identical column Bs (items), I need to delete the oldest row based on the
date in Column A. And then do this for the entire spreadsheet. Please see
example below. Is this possible?

Any help is greatly appreciated. Thank You!

Date Description
10/1/2006 AAX1
1/15/2007 AAX1

Would want to delete the row with the date of 10/1/2006.

Macro to Delete Certain Rows
 

Sub DeleteRows()
Dim iLastRow As Long
Dim i As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If .Cells(i, "B").Value = .Cells(i - 1, "B").Value Then
If .Cells(i, "A").Value .Cells(i - 1, "A").Value Then
.Rows(i - 1).Delete
Else
.Rows(i).Delete
End If
End If
Next i
End With
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"HROBERTSON" wrote in message
...
I have a spreadsheet that I need a macro for that will go through the
spreadsheet and delete lines that are nearly duplicate. For example,
column
A will be a date, and column B an item. If there are two rows that have
identical column Bs (items), I need to delete the oldest row based on the
date in Column A. And then do this for the entire spreadsheet. Please see
example below. Is this possible?

Any help is greatly appreciated. Thank You!

Date Description
10/1/2006 AAX1
1/15/2007 AAX1

Would want to delete the row with the date of 10/1/2006.

Macro to Delete Certain Rows
 

I bet you wanted to start at the bottom and work your way to the top:

For i = 2 To iLastRow
would be:
For i = iLastRow to 2 step -1

Bob Phillips wrote:

Sub DeleteRows()
Dim iLastRow As Long
Dim i As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If .Cells(i, "B").Value = .Cells(i - 1, "B").Value Then
If .Cells(i, "A").Value .Cells(i - 1, "A").Value Then
.Rows(i - 1).Delete
Else
.Rows(i).Delete
End If
End If
Next i
End With
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"HROBERTSON" wrote in message
...
I have a spreadsheet that I need a macro for that will go through the
spreadsheet and delete lines that are nearly duplicate. For example,
column
A will be a date, and column B an item. If there are two rows that have
identical column Bs (items), I need to delete the oldest row based on the
date in Column A. And then do this for the entire spreadsheet. Please see
example below. Is this possible?

Any help is greatly appreciated. Thank You!

Date Description
10/1/2006 AAX1
1/15/2007 AAX1

Would want to delete the row with the date of 10/1/2006.

--

Dave Peterson