Macro to check against Columns & Delete Duplicates

jolivanes · #1 12-14-2017, 08:49 AM

Attach your workbook. Change numbers but not the formatting etc.
In an empty column to the right in the first cell, put "=Len(A1)"
Drag formula 4 columns to the right and however many rows you have in Columns A to D
All numbers should correspond to the amount you need it to be. 14 For Columns A, B and C and 12 For Column D

jolivanes · #2 12-14-2017, 09:35 AM

If you do have leading or trailing spaces, this should take care of it as part of the code.

Code:

Sub cjamps_B()
Dim i As Long, c As Range, rng As Range
Application.ScreenUpdating = False
    Set rng = Range("A2:D" & Cells(Rows.Count, "D").End(xlUp).Row)    '<----- Range with ALL phone numbers
    rng.Value = Application.Trim(rng)    '<---- Trims a range instead of looping cells
    With Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        .Offset(, 1).Formula = "=IF(LEN(RC[-1])=13,RIGHT(RC[-1],12),RC[-1])"
        .Offset(, 1).Value = .Offset(, 1).Value
        .Offset(, 2).Formula = "= ""(""&LEFT(RC[-1],3)&"")""&"" ""&MID(RC[-1],5,3)&"" ""&MID(RC[-1],9,4)"
        .Offset(, 2).Value = .Offset(, 2).Value
    End With
    For Each c In Range("F2:F" & Cells(Rows.Count, "F").End(xlUp).Row)
        For i = 2 To Cells(Rows.Count, "F").End(xlUp).Row
            If WorksheetFunction.CountIf(Range(c.Offset(, -5).Address & ":" & c.Offset(, -3).Address), c.Value) <> 0 Then c.Offset(, -2).ClearContents: Exit For
        Next i
    Next c
    ActiveSheet.UsedRange.Columns("E:F").Offset(1).ClearContents
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