Hi jolivanes,
Thanx for posting. I tried the macros but couldn't get it to work. The first macro left rows of () in column G and the second macro didn't do anything. The duplicate phone numbers that have to be deleted are in column D.
The following code works for me even though it is slow with 2 issues:
1. I only want it to leave one instance of the phone number in column D if it doesn't exist. (I have to do it manually through excel.)
2. Sometimes when I paste the phone numbers into column D for some reason they have a 1 in front of the area code. (example format:1999-999-9999). Right now I have to delete the 1 manually for the macro to work.
Code:
Sub ClearDups()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim phone As Range
Dim foundPhone As Range
Dim sPhone As String
Dim rng As Range
For Each phone In Range("D2:D" & LastRow)
sPhone = "(" & Left(phone, 3) & ") " & Mid(phone, 5, 8)
Set foundPhone = Range("A2:C" & LastRow).Find(sPhone, LookIn:=xlValues, lookat:=xlWhole)
If Not foundPhone Is Nothing Then
phone.ClearContents
End If
Next phone
Application.ScreenUpdating = True
End Sub