Delete if duplicate

[khalid79m]

    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]

Is BCDU12_Duplicates a column?

[khalid79m]

Hi mate it's a column (named range)

[mdmackillop]

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

[VBA]
Option Compare Text
Sub DelDups()

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

End Sub
[/VBA]

[khalid79m]

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,