Find duplication Macro

D

Duncan_J

Hi Folks I use this to find me dups, I just highlight the Column and go:

Sub FixDuplicateRows()
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).Value = ""
End If
Next RowNdx
End Sub

However, if I have a number in row 2 and row 10 it doesn't get ride of the
number. With the marco I'm using the number has to be directly below it..
Anyone got a fix?
Thanks
DJ
 
T

Tom Ogilvy

Sub FixDuplicateRows()
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
set rng = Range(selection(1),Cells(rowNdx-1,ColNum))
If Application.countif(rng,Cells(RowNdx, ColNum).Value) >= 1 Then
Cells(RowNdx, ColNum).ClearContents
End If
Next RowNdx
End Sub
 
T

Tom Ogilvy

forgot to declare rng:

Sub FixDuplicateRows()
Dim RowNdx As Long
Dim ColNum As Integer
Dim rng As Range
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
Set rng = Range(Selection(1), Cells(RowNdx - 1, ColNum))
If Application.CountIf(rng, Cells(RowNdx, ColNum).Value) >= 1 Then
Cells(RowNdx, ColNum).ClearContents
End If
Next RowNdx
End Sub
 
D

Duncan_J

Thanks Tom

Tom Ogilvy said:
forgot to declare rng:

Sub FixDuplicateRows()
Dim RowNdx As Long
Dim ColNum As Integer
Dim rng As Range
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
Set rng = Range(Selection(1), Cells(RowNdx - 1, ColNum))
If Application.CountIf(rng, Cells(RowNdx, ColNum).Value) >= 1 Then
Cells(RowNdx, ColNum).ClearContents
End If
Next RowNdx
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top