Excel Macro Running But Not Working!

[cjamps]

I have received 2 macros from other users to check a 4th column against the first 3 in excel for duplicates. While they do run they are not working. My spreadsheet has the 4 columns and the data starts in the second row.
Can anyone please advise.

[COLOR=#000000]Sub ClearDups()[/COLOR]
[COLOR=#000000]   Application.ScreenUpdating = False[/COLOR]
[COLOR=#000000]   Dim LastRow As Long[/COLOR]
[COLOR=#000000]   LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/COLOR]
[COLOR=#000000]   Dim phone As Range[/COLOR]
[COLOR=#000000]   Dim rng As Range[/COLOR]
[COLOR=#000000]   For Each phone In Range("D2:D" & LastRow)[/COLOR]
[COLOR=#000000]       For Each rng In Range("A" & phone.Row & ":C" & phone.Row)[/COLOR]
[COLOR=#000000]           If phone = Mid(rng, 2, 3) & "-" & Mid(rng, 7, 99) Then[/COLOR]
[COLOR=#000000]               phone.ClearContents[/COLOR]
[COLOR=#000000]               Exit For[/COLOR]
[COLOR=#000000]           End If[/COLOR]
[COLOR=#000000]       Next rng[/COLOR]
[COLOR=#000000]   Next phone[/COLOR]
[COLOR=#000000]   Application.ScreenUpdating = True[/COLOR]
[COLOR=#000000]End Sub 

[/COLOR]
[COLOR=#000000]Sub ClearDupesInC() 'cjamps[/COLOR]
[COLOR=#000000]Dim LastC As Long, LastB As Long, i As Long, j As Long, n As Long[/COLOR]

[COLOR=#000000]Application.ScreenUpdating = False[/COLOR]
[COLOR=#000000]LastC = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row LastB = ActiveSheet.UsedRange.Rows.Count[/COLOR]

[COLOR=#000000]For n = 3 To 4[/COLOR]

[COLOR=#000000]For i = LastC To 2 Step -1       'if you have no header, go from LastC to 1[/COLOR]
[COLOR=#000000]   If Left(Cells(i, n).Value, 1) = 1 Then[/COLOR]
[COLOR=#000000]       Cells(i, n).Value = Right(Cells(i, n).Value, Len(Cells(i, n).Value) - 1)[/COLOR]
[COLOR=#000000]   End If[/COLOR]
[COLOR=#000000]   If Cells(i, n).Value <> "" And _[/COLOR]
[COLOR=#000000]Application.WorksheetFunction.CountIf(Cells(LastC, n), Cells(i, n).Value) > 1 Then[/COLOR]
[COLOR=#000000]       Cells(i, n).ClearContents[/COLOR]
[COLOR=#000000]   End If[/COLOR]
[COLOR=#000000]   If Cells(i, n).Value <> "" Then[/COLOR]
[COLOR=#000000]   For j = LastB To 2 Step -1   'if you have no header, go from LastB to 1[/COLOR]
[COLOR=#000000]       If (Right(Cells(i, n).Value, 8) = Right(Cells(j, n - 1).Value, 8) And _[/COLOR]
[COLOR=#000000]       InStr(2, Left(Cells(j, n - 1).Value, 4), Left(Cells(i, n).Value, n))) _[/COLOR]
[COLOR=#000000]       Or (Right(Cells(i, n).Value, 8) = Right(Cells(j, 1).Value, 8) And _[/COLOR]
[COLOR=#000000]       InStr(2, Left(Cells(j, 1).Value, 4), Left(Cells(i, n).Value, n))) Then[/COLOR]
[COLOR=#000000]           Cells(i, n).ClearContents[/COLOR]
[COLOR=#000000]       End If[/COLOR]
[COLOR=#000000]   Next j[/COLOR]
[COLOR=#000000]   End If[/COLOR]
[COLOR=#000000]Next i[/COLOR]
[COLOR=#000000]Next n[/COLOR]
[COLOR=#000000]Application.ScreenUpdating = True[/COLOR]
[COLOR=#000000]End Sub[/COLOR]

 

[mumps]

I suggested this macro:

Sub ClearDups()
   Application.ScreenUpdating = False
   Dim LastRow As Long
   LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Dim phone As Range
   Dim rng As Range
   For Each phone In Range("D2:D" & LastRow)
       For Each rng In Range("A" & phone.Row & ":C" & phone.Row)
           If phone = Mid(rng, 2, 3) & "-" & Mid(rng, 7, 99) Then
               phone.ClearContents
               Exit For
           End If
       Next rng
   Next phone
   Application.ScreenUpdating = True
End Sub

When I tried it on some dummy data, it worked as you requested in your original thread. Can you post a screen shot of what your data looks like? See Section B at this link on how to add screen shots: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[cjamps]

Sheet1

	A	B	C	D
Phone Home	Phone Mobile	Hotline
(999) 999-9999	(999) 999-9991	(999) 999-9992	999-999-9994
	(999) 999-9998		999-999-9992
		(999) 999-9997	999-999-9993

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:118px;"><col style="width:101px;"><col style="width:101px;"><col style="width:95px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: center"]Phone Other[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This is it. I press ALT+11 and the run with your macro on the screen. It seems to be running but nothing is happening.

 

[mumps]

Nothing is happening because there are no duplicates of the fourth column in the first three columns. I assumed that the duplicates had to be in the same row as that of the number in the fourth column. Can the duplicates be in any row? For example, would you expect 999-999-9992 in row 3 of column D to be cleared because it is duplicated in row 2 of column C?

 

[cjamps]

Yes. I do apologize for causing any misunderstanding I had caused. Because I get the numbers sent to me by email I paste them into excel in column D as is. Rows A,B & C need to remain set columns and not be changed by the macro.

Thank you for your time and effort.

 

[mumps]

Try:

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