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.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=395321

delete rows if value in column D is repeated
 

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

delete rows if value in column D is repeated
 

Hi DKY,

Maybe something here will help:

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

Regards,

Patti



"DKY" wrote in message
...

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

delete rows if value in column D is repeated
 

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

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=39532

delete rows if value in column D is repeated
 

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

delete rows if value in column D is repeated
 

This one works fine, you just highlight the cells in the column tha
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)
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 th
duplicates in it. That way you wouldn't have to manually selec
anything. You know, if you were going to use this in an excel fil
that gets output every week or month or something and the column tha
contains the data never changes.

Really cool macro, thanks Patti

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=39532