it appears to do what you want here, but i don't have your dataset.

anyway, i would qualify all of the ranges and use .value for the cell
references. i usually do my if's on different lines so it's easier for me read
and to create breakpoints


Sub DeleteDuplicates2Columns()
Dim LR As Long, i As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = Worksheets("Sheet1")
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 2 Step -1
If ws.Cells(i, "A").Value = ws.Cells(i - 1, "A").Value And ws.Cells(i,
"L").Value = ws.Cells(i - 1, _
"L").Value Then
ws.Rows(i).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub


--


Gary Keramidas
Excel 2003


"Monomeeth" wrote in message
...
Hello

I have a macro I have been using to delete duplicate rows which contain the
same value in two columns. This works well, as long as I manually sort Column
A in ascending order first, before running the macro. The code is as follows:

--------------------------

Sub DeleteDuplicates2Columns()
Dim LR As Long, i As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 2 Step -1
If Cells(i, "A") = Cells(i - 1, "A") And _
Cells(i, "L") = Cells(i - 1, "L") Then _
Rows(i).Delete xlShiftUp
Next i

Application.ScreenUpdating = True
End Sub

--------------------------

As you can see, the above code removes duplicate rows if they contain
matching data in columns A and L. In the past if I have needed to change this
to remove duplicate rows based on matching values in different columns I just
modified the code. However, I now want to share this with other users who
have no idea about code, so my preference would be to have an input box where
they can just select the columns.

Also, in the past I have just added another line of code if I wanted the
macro to do the comparisons across three columns. For example:

For i = LR To 2 Step -1
If Cells(i, "A") = Cells(i - 1, "A") And _
Cells(i, "L") = Cells(i - 1, "L") Then _
Rows(i).Delete xlShiftUp
Next i

BECOMES

For i = LR To 2 Step -1
If Cells(i, "A") = Cells(i - 1, "A") And _
Cells(i, "K") = Cells(i - 1, "K") And _
Cells(i, "L") = Cells(i - 1, "L") Then _
Rows(i).Delete xlShiftUp
Next i

So, is there any way to do this in a user-friendly manner for other users?
For instance, using an input box to specify how many columns the user wants
to interrogate and then having the macro display enough input boxes for the
user to actually specify the columns. Perhaps this could work if the number
of columns were restricted to a maximum of 5?

Just out of curiosity, can someone explain why I have to sort Column A into
ascending order first before running the macro? Is this macro limited in some
way so that it only compares the rows one at a time adjacent to one another?
If so, how could I modify the macro so that this isn't the case? Perhaps it
would be easier to just get the macro to sort Column A first before doing
anything else.

Sorry for the long post. Hope this all makes sense. Any help would be
greatly appreciated!

Thanks!

Joe.
--
If you can measure it, you can improve it!