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)
