Macro to check against Columns & Delete Duplicates

cjamps · #5 12-12-2017, 02:03 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA: Delete duplicates in each row	bandaanders	Excel Programming	2	09-02-2015 08:15 AM
Excel vba to check to check if two columns are empty	subspace3	Excel Programming	5	07-09-2015 04:45 PM
Macro to keep first instance and remove duplicates in certain column	zhead	Excel	2	03-18-2015 10:16 AM
find and delete duplicates	rcVBA	Word VBA	4	05-15-2013 03:08 PM
Deleting Duplicates in Macro	jillapass	Excel Programming	1	01-11-2012 10:02 AM