Remove Duplicates Based on more than one column
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. |
Remove Duplicates Based on more than one column
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 "Michelle" wrote: 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. |
Remove Duplicates Based on more than one column
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" 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 "Michelle" wrote: 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. |
All times are GMT +1. The time now is 12:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com