PDA

View Full Version : Delete the duplicate value/cell in selected range



naris
08-10-2009, 07:37 PM
Dear all my friends in this forum,

I need your help to solve my problem, I want delete the duplicate value/cell in selected range.

Thanks.
Naris

GTO
08-10-2009, 08:02 PM
Greetings Naris,

Presuming you mean to delete both of the duplicate values, try:

In a Standard Module:

Option Explicit

Sub DelDups()
Dim _
arySelection As Variant, _
aryTemp() As Boolean, _
x As Long, _
y As Long


If Selection.Count > 1 Then

arySelection = Selection.Value

ReDim aryTemp(1 To UBound(arySelection, 1), 1 To UBound(arySelection, 2))

For x = 1 To UBound(arySelection, 1)
For y = 1 To UBound(arySelection, 2)
If Application.WorksheetFunction _
.CountIf(Selection, arySelection(x, y)) > 1 Then

aryTemp(x, y) = True
End If
Next
Next
For x = 1 To UBound(arySelection, 1)
For y = 1 To UBound(arySelection, 2)
If aryTemp(x, y) = True Then
arySelection(x, y) = Empty
End If
Next
Next

Selection.Value = arySelection
End If
End Sub


Hope that helps,

Mark

naris
08-10-2009, 08:38 PM
Thanks for your quick response & help, it will helpful.

Naris

GTO
08-10-2009, 08:49 PM
Happy to help :friends:

If that does the trick, you can mark the thread as solved using the Thread Tools button right above your first post:)

Mark

Aussiebear
08-11-2009, 04:36 AM
I may be reading this wrong Mark, but my impression was that the OP is looking to delete the duplicate cell within the range ( as in the recurring cell value rather than both values). But time will tell if I'm wrong.

GTO
08-11-2009, 05:57 PM
:hi: Hi Ted

Hmmm... I may have misread #3.

@Naris:

Did that do the trick, or were we looking to leave one of ea given val?

Mark