Remove Duplicates Based on more than one column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the follwoing data and need to remove the duplicates based on name
and date.
Example Data
Name Date
Jim Smith 7-1-07
Jim Smith 7-1-07
Jim Smith 7-1-07
Jim Smith 7-5-07
Jim Smith 7-5-07
Nancy Smith 7-5-07
Nancy Smith 7-5-07
Nancy Smith 7-5-07

this is the code I have now but is based on 1 column and 1 row.
 
my code sorry

Sub RemoveDupes()
With Cells

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
rng.Select
End With

Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).EntireRow.Delete shift:=xlUp
End If
Next RowNdx
End Sub
 
Sub RemoveDupes_R1()
Dim rng As Range
Dim RowNdx As Long
Dim ColNum As Long
Dim strText As String
Dim strAbove As String

Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
ColNum = rng.Column
Application.ScreenUpdating = False

For RowNdx = rng(rng.Count).Row To rng.Row + 1 Step -1
strText = Cells(RowNdx, ColNum).Text & Cells(RowNdx, ColNum + 1).Text
strAbove = Cells(RowNdx - 1, ColNum).Text & Cells(RowNdx - 1, ColNum + 1).Text
If strText = strAbove Then Rows(RowNdx).Delete shift:=xlUp
Next 'RowNdx

Application.ScreenUpdating = True
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Michelle" <[email protected]>
wrote in message
my code sorry

Sub RemoveDupes()
With Cells

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
rng.Select
End With

Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).EntireRow.Delete shift:=xlUp
End If
Next RowNdx
End Sub
 
Back
Top