macro - Find duplicate in column - delete line

[skyport]

Hi everyone.

What I have is a situation where data is inputted into columns A to G. Each line of data will include a telephone number in Column D.

If possible, I would like to have a macro that searches the column D and when it finds a duplicate telephone number, it will delete the entire rows that contain the duplicate telephone numbers so those lines are completely gone leaving only the one line where that telephone number first appears in column D along with leaving of course all lines where that tel number was never listed in the first place.

Can this be done?

 

[skyport]

Rick,

Excel version is 2000. Just ran the first code version as well and it gives a runtime error with debug focusing or alerting to the following line:

Intersect(Columns("D").SpecialCells(xlConstants, xlErrors). _
EntireRow, Columns("A:G")).Delete xlShiftUp

 

[Rick Rothstein]

The version I tried was the second one. I will try the first one now and see any difference. The current number of lines is 120 but that increases every week.

Sorry, I had that second code looking in the wrong column for duplicates (it was looking in Column A instead of Column D)... see if this revised code works for you.

Sub MyDeleteCode()
  Dim R As Long, C As Long, Counter As Long, LastRow As Long
  Dim Data As Variant, Result As Variant, Dict As Object
  Set Dict = CreateObject("Scripting.Dictionary")
  LastRow = Cells(Rows.Count, "D").End(xlUp).Row
  Data = Range("A2:G" & LastRow).Formula
  ReDim Result(1 To UBound(Data), 1 To 7)
  On Error Resume Next
  For R = 1 To UBound(Data)
    Dict.Add Data(R, 4), CStr(Data(R, 4))
    If Err.Number Then
      Data(R, 4) = "#N/A"
      Err.Clear
    End If
  Next
  On Error GoTo 0
  Set Dict = Nothing
  For R = 1 To UBound(Data)
    If Data(R, 4) <> "#N/A" Then
      Counter = Counter + 1
      For C = 1 To 7
        Result(Counter, C) = Data(R, C)
      Next
    End If
  Next
  Range("A2:G2").Resize(UBound(Result)) = Result
End Sub

 

[Joe4]

Joe, there are other macros to the sheet however, nothing automated. they all need to be run manually.

You may have something else going on then.
I tried running it on over 1000 rows worth of data, and it took less than a second.

Could be the old version of Excel, could be the "power" (or lack there-of) of your computer.
Without access to the file and environment, I really cannot say.

 

[skyport]

To Rick,

That last change you made did the trick. Version 2 now Works perfectly without a snag. Thanks as always.

 

[skyport]

To Joe,

I would be at a loss as well to determine why it did what it did even though in the end after the time I stated, the result was there after several minutes. Between that code and the filer idea previously, they got me through the last day and a half so I could keep going forward with what I was doing. For this reason, I thank you much for your help and look forward to meeting here again in the future. You, Rick and all the rest on this site are just great.