MrExcel Publishing
Your One Stop for Excel Tips & Solutions

deleting duplicated rows from a mailing list


Posted by Naheed on January 14, 2002 7:34 PM

I have a list of names in one column and corresponding addresses in the next column, on the way to creating a mailing list. Problem, there are lots of duplicates. How do I get excel to eliminate the duplicate rows? Any help would be much appreciated. Cheers,

:-N


Posted by Jacob on January 14, 2002 8:14 PM

Hi

This should do the trick:
Assuming Name Column is A

Sub DeleteDuplicates()

Dim x As Integer
Dim LastRow As Integer
Dim c As Range
Dim MyValue As String
Dim FirstAddress As String

On Error Resume Next

LastRow = Range("A65536").End(xlUp).Row

For x = 1 To LastRow

MyValue = Range("A" & x).Value

If MyValue = "zzzzz" Then
Else

With Range("A1:A" & LastRow)

Set c = .Find(What:=MyValue, LookIn:=xlValues, LookAt:=xlWhole)

If Not c Is Nothing Then
FirstAddress = c.Address
Do

If c.Address = FirstAddress Then
Else
Range(c.Address).Value = "zzzzz"
End If

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
Else
End If

End With

End If

Next x

Range("A1").Select

Do

If ActiveCell.Value = "zzzzz" Then
Rows(ActiveCell.Row).Delete Shift:=xlUp
ActiveCell.Offset(-1, 0).Select
Else
End If

ActiveCell.Offset(1, 0).Select

Loop While ActiveCell.Row < LastRow + 1

End Sub


HTH

Jacob

Posted by Valeria on January 15, 2002 12:36 AM


Assuming your data is in Columns A & B :-

Sub DeleteDuplicates()
Application.ScreenUpdating = False
Columns("A:A").Insert
With Range(Range("B2"), Range("B65536").End(xlUp)).Offset(0, -1)
.FormulaR1C1 = "=IF(RC[1]&RC[2]=R[-1]C[1]&R[-1]C[2],1,"""")"
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
.EntireColumn.Delete
End With
End Sub


Posted by Valeria on January 15, 2002 12:49 AM

Note : The data needs to be sorted before running the macro (nt)