delete rows if value in column D is repeated

I don't even know how to put this into words to search. I tried searching in the forums because I'm positive that someone has already asked for help on this before but I need something that's going to look through my sheet and find all duplicate values in column D and delete the entirerow. I'm finding that I have huge spreadsheets that have multiple values and I can sort it and try to find them with the human eye but I'm sure I'll miss a lot. So, I'm not even sure how I would do this. Any help is very appreciated. Thanks in advance.

I found this, but it doesn't quite seem to be working.

Sub del()
Dim j As Long
Dim i As Long
j = Cells(Rows.Count, 4).End(xlUp).Row
For i = j To 1 Step -1
If WorksheetFunction.CountIf(Range("D1:D" & j), Cells(i, 1).Value) > 1 Then
Cells(i, 1).EntireRow.delete
End If
Next i
End Sub

Hi DKY,

Maybe something here will help:

http://www.cpearson.com/excel/deleti...eDuplicateRows

Regards,

Patti



"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> I found this, but it doesn't quite seem to be working.
>
> Sub del()
> Dim j As Long
> Dim i As Long
> j = Cells(Rows.Count, 4).End(xlUp).Row
> For i = j To 1 Step -1
> If WorksheetFunction.CountIf(Range("D1:D" & j), Cells(i, 1).Value) > 1
> Then
> Cells(i, 1).EntireRow.delete
> End If
> Next i
> End Sub
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
> http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=395321
>

That works beautifully!!!! Thank you very much!!!

I'm not sure if this will help or not, it's a macro I use a lot. As long
as the data is sorted just highlight all the cells in column D and run
this. It might not be perfect, but it should get you in the ball park.

Sub DeleteDuplicate()
'
' DeleteDuplicate Macro
' Macro recorded 1/11/2005 by Paul H. Trively III
'
' Keyboard Shortcut: Ctrl+p
'


Application.ScreenUpdating = False
counter = 0
RNG = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
baseRow = ActiveCell.Row

For i = 1 To RNG
myCellValue = ActiveCell.Value
myNextCellValue = ActiveCell.Offset(1, 0).Value
If ActiveCell.Value = myNextCellValue Then
For X = 1 To 5000
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = myNextCellValue Then
counter = counter + 1
Else
X = 5001
End If
Next X
toBaseRow = ActiveCell.Row - 1

ActiveCell.Offset(-counter, 0).Select

Range("A" & baseRow + i, "A" & toBaseRow).Select
Selection.EntireRow.Delete
counter = 0
'If the next cell is the same project number then add it's
value to current and delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i

Application.ScreenUpdating = True

End Sub

This one works fine, you just highlight the cells in the column that have the duplicates in them.

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

You could even put a

LRow = Cells(Rows.Count, "D").End(xlUp).Row
Range("D2:D" & LRow).Select

at the top of it and change the D's to whatever column has the duplicates in it. That way you wouldn't have to manually select anything. You know, if you were going to use this in an excel file that gets output every week or month or something and the column that contains the data never changes.

Really cool macro, thanks Patti.