The Problem :

Suppose I have the following data arranged in rows.

5
2
3
3
3
3
4
2
3
3
3
4
5
6
6

I wish to delete duplicate values (rows) so that the list becomes :

5
2
3
4
2
3
4
5
6

I do not wish to delete for example all the 3's except one but delete all duplicate 3's from each group of 3's leaving a single 3 in each group.
Similarly with other groups e.g in this case the number 6.
I want to automate this process.
Note all the groups contain consecutive duplicates.
Can this be done using an excel formula or excel function or vba code ?

I welcome any suggestions

This will do it for your Numbers, if you want to do the check for Text you need to change the Dim of the variable lookfor from "Integer" to "String", without the quotes of course.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 03/04/2007 by A C Osborn
'
' Keyboard Shortcut: Ctrl+a
'
Dim count As Integer, lookfor As Integer, lastrow As Integer
lastrow = Cells(Rows.count, "A").End(xlUp).Row
lookfor = Cells(lastrow, 1)
For count = (lastrow - 1) To 1 Step -1
MsgBox lookfor & " - " & Cells(count, 1)
If Cells(count, 1) = lookfor Then

Cells(count, 1).EntireRow.Delete xlShiftUp
Else
lookfor = Cells(count, 1)
'lastrow = lastrow - 1
End If
Next

End Sub

You do not actually need the msgbox, it is only there to show you the values that are being checked in each step. You can either delete it or REM it out by placing a "'" at the start of that line.

Thank you OBP - your method does exactly what is required - many thanks for this and other answers you have given me in the past.

Regards