Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplication Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplication Macro
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 -- Regards, Tom Ogilvy "Duncan_J" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplication Macro
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 -- Regards, Tom Ogilvy "Duncan_J" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplication Macro
Thanks Tom
"Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Duncan_J" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplication Macro | Excel Discussion (Misc queries) | |||
Duplication | Excel Discussion (Misc queries) | |||
Range Name Duplication and PasteSpecial Macro | Excel Programming | |||
I need to find a macro to find data cut and paste to another colu. | Excel Programming | |||
Excel macro - Help with duplication validation macro | Excel Programming |