PDA

View Full Version : Delete if duplicate



khalid79m
02-05-2009, 11:02 AM
Dim rCell As Range

For Each rCell In Sheets("BCDU").Range("BCDU12_Duplicates").Cells

If UCase(rCell.Value) = "Duplicate" Then
rCell.EntireRow.Delete Shift:=xlUp
End If

Next


I have the above good to look at the sheet BCDU range BCDU12_Duplicates and go through the range until you find the word duplicate the delete the entire row, keep going till the end of the range...

have i done something wrong pleae help thanks

lucas
02-05-2009, 11:11 AM
Is BCDU12_Duplicates a column?

khalid79m
02-05-2009, 01:55 PM
Hi mate it's a column (named range)

mdmackillop
02-05-2009, 02:26 PM
Your main problem is that "Duplicate" is not UCASE in your code. The code will not clear all Duplicate values unless you loop from the bottom up. Filter is a better solution


Option Compare Text
Sub DelDups()

Sheets("BCDU").Range("BCDU12_Duplicates").AutoFilter Field:=1, Criteria1:="Duplicate"
Sheets("BCDU").Range("BCDU12_Duplicates").SpecialCells(xlCellTypeVisible).EntireRow.Delete

End Sub

khalid79m
02-06-2009, 07:39 AM
Firstly your a genuis, nice one mate works like a treat.

one problem, the filter is always place on the row below the header and hence filters that first result to be deleted as well.

I have a macro which runs though column a:iv and uses the headers for the named ranges. when I run your filter instead of using row where the headers are it uses row 2


Dim oneCell As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For Each oneCell In ThisWorkbook.Sheets("O_BCDU").Range("A1:V1")
With oneCell
If CStr(.Value) <> vbNullString Then
.Offset(1, 0).Resize(Lastrow - 1, 1).Name = CStr(.Value)
End If
End With
Next oneCell


its only 1 result lost but it would be good if i didnt loose it,