Solved: Macro to delete duplicate rows on condition

Hi there,
I’m new to this forum and also a complete novice in Excel so I’m sorry if my macro problem is too simple…

I need a macro to delete duplicate rows on a worksheet but it needs to check between two columns instead of just one.

To try and explain exactly what’s required I’ve come up with this basic example:

Column A will be for ‘Type’ and will only be one of the following values: 1, 2, 3 or 4
Column B will be for ‘Title’ and can be any value

I need the macro to delete duplicate rows based on what’s in Column B but only if Column A is 3 or 4 not 1 or 2 and if there are duplicate entries under 3 as well as 4, I only need the first entry under Type 3 to be kept and all other entries under Type 3 and 4 to be deleted. All entries under Types 1 & 2 need to be shown, including duplicates.

For instance; if a title appears in Column B say 6 times but one entry is under Type 1 and two entries are under Types 2, 3 & 4 each I need all the entries under Types 1 & 2 to be kept and only one entry under Type 3 to be kept.
e.g.

Column A Column B
Type 1 Title X
Type 3 Title X
Type 4 Title X
Type 4 Title X
Type 2 Title X
Type 3 Title X
Type 2 Title X

Should show:
Column A Column B
Type 1 Title X
Type 3 Title X
Type 2 Title X
Type 2 Title X

If there is an entry under Type 4, however, and none under Type 3 then the Type 4 record will need to be shown.

I’ve found a few macro codes online but could only get one to work at all (found here http://www.cpearson.com/excel/deleting.htm) but this removed all duplicates based on Column B so removed the lines that are still required under a different Type from Column A.

Hopefully this makes sense!

Thanks,

Nick

Run the following, and if it selects the right rows to delete, change Select to Delete:[vba]Sub blah()
Dim RowsToDelete As Range
Set TheRng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:B"))
'Set TheRng = Selection 'you can use this line instead of the above if you select a 2-column range to process before running this macro.
'TheRng.Select 'debug line, can remove.
xx = TheRng.Value
For i = UBound(xx) To 2 Step -1
If xx(i, 1) = "Type 3" Or xx(i, 1) = "Type 4" Then
For j = i - 1 To 1 Step -1
If (xx(j, 1) = "Type 3" Or xx(j, 1) = "Type 4") And xx(i, 2) = xx(j, 2) Then
Set RowsToDelete = Union(IIf(RowsToDelete Is Nothing, TheRng.Cells(i, 1), RowsToDelete), TheRng.Cells(i, 1))
Exit For
End If
Next j
End If
Next i
RowsToDelete.EntireRow.Select 'Delete
End Sub
[/vba]You may need to change what's in the double quotes to match what's in your column A, but it should get you started.

Thanks alot for your really speedy and helpful response and best of all it works!

Thanks for your help, very much appreciated.

Marked as solved!