Solved: Macro to delete duplicate rows on condition

[nickirvine]

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

[p45cal]

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.

[nickirvine]

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!