VB Paste COUNTIF into range to find duplicates

[gtvkeith]

Hi everyone.

I am having some trouble with an error in this code.
I have a list of visitors names that grows everyday.
I want to use this list in a drop-down box, but eliminate any duplicate entries first.
This code will be in the Autpen macro and so recompile the list when the workbook is opened.

I have two problems
1. I get a type mismatch error on "If d.Value > 1 Then"
2. The formula in that is pasted in Column A has a "NAME?" error on the first part of the B:B range that I have made BOLD in the code.

'Paste COUNTIF formula
    Sheets("VisitorRef").Select
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    Range("A1:A" & LastRow).FormulaR1C1 = "=COUNTIF([B]B[/B]:B,RC[+1])"
        
'Remove duplicate entries:
    For Each d In CountRange
        If d.Value > 1 Then
            d.EntireRow.Delete = True
        End If
    Next

I know it will probably be something simple but once again I am stumped and any help will be greatly appreciated.

Keith

 

[Airfix9]

You need to delete rows backwards, starting from the bottom of the range and going upwards. You know the last row so use this instead:

dim r as long
For r = lastrow to 1 step -1

if .cells(r,1).value > 1 then rows(r).entirerow.delete

next r

That should solve it.

 

[Marcelo Branco]

Hi,

Maybe this can help (a different approach to the same problem)

http://j-walk.com/ss/excel/tips/tip47.htm

HTH

M.

 

[gtvkeith]

Thankyou guys,

That seems to be working okay.

Keith.