Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate removal macro keeps newest record
Hi
I am using the following code to remove duplicate numbers in a column (deleting the whole row of data), but I am having a problem that the record being kept is the most recent by date. I would like to keep the record by date when it was first identified ie the record from January 1st rather than Jan 5th. the following is the code I am using, I apologise in advance if the answer is staring me in the face my knowledge of VBA is pretty basic and this is is code supplied here by another poster to this group (cant remember the persons name so I apologise for not referencing you). Columns("A:A").Select Dim R As Long Dim N As Long Dim V As Variant Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set Rng = Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Columns(ActiveCell.Column)) Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0") N = 0 For R = Rng.Rows.Count To 2 Step -1 If R Mod 500 = 0 Then Application.StatusBar = "Processing Row: " & Format(R, "#,##0") End If V = Rng.Cells(R, 1).Value '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''' ' Note that COUNTIF works oddly with a Variant that is equal to vbNullString. ' Rather than pass in the variant, you need to pass in vbNullString explicitly. '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''' If V = vbNullString Then If Application.WorksheetFunction.CountIf(Rng.Columns( 1), vbNullString) 1 Then Rng.Rows(R).EntireRow.Delete N = N + 1 End If Else If Application.WorksheetFunction.CountIf(Rng.Columns( 1), V) 1 Then Rng.Rows(R).EntireRow.Delete N = N + 1 End If End If Next R EndMacro: Application.StatusBar = False Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Thanks very much for any help Eddie |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking cells/duplicate removal | Excel Discussion (Misc queries) | |||
Auto Removal Of Duplicate Rows | Excel Worksheet Functions | |||
macro that identify the newest file in a folder and open it. | Excel Programming | |||
exel macro to eliminate duplicate record | Excel Programming | |||
Duplicate Row Removal Solution | Excel Programming |